1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42
  43class _Expression(type):
  44    def __new__(cls, clsname, bases, attrs):
  45        klass = super().__new__(cls, clsname, bases, attrs)
  46
  47        # When an Expression class is created, its key is automatically set to be
  48        # the lowercase version of the class' name.
  49        klass.key = clsname.lower()
  50
  51        # This is so that docstrings are not inherited in pdoc
  52        klass.__doc__ = klass.__doc__ or ""
  53
  54        return klass
  55
  56
  57SQLGLOT_META = "sqlglot.meta"
  58TABLE_PARTS = ("this", "db", "catalog")
  59
  60
  61class Expression(metaclass=_Expression):
  62    """
  63    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  64    context, such as its child expressions, their names (arg keys), and whether a given child expression
  65    is optional or not.
  66
  67    Attributes:
  68        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  69            and representing expressions as strings.
  70        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  71            arg keys to booleans that indicate whether the corresponding args are optional.
  72        parent: a reference to the parent expression (or None, in case of root expressions).
  73        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  74            uses to refer to it.
  75        comments: a list of comments that are associated with a given expression. This is used in
  76            order to preserve comments when transpiling SQL code.
  77        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  78            optimizer, in order to enable some transformations that require type information.
  79        meta: a dictionary that can be used to store useful metadata for a given expression.
  80
  81    Example:
  82        >>> class Foo(Expression):
  83        ...     arg_types = {"this": True, "expression": False}
  84
  85        The above definition informs us that Foo is an Expression that requires an argument called
  86        "this" and may also optionally receive an argument called "expression".
  87
  88    Args:
  89        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  90    """
  91
  92    key = "expression"
  93    arg_types = {"this": True}
  94    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  95
  96    def __init__(self, **args: t.Any):
  97        self.args: t.Dict[str, t.Any] = args
  98        self.parent: t.Optional[Expression] = None
  99        self.arg_key: t.Optional[str] = None
 100        self.comments: t.Optional[t.List[str]] = None
 101        self._type: t.Optional[DataType] = None
 102        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 103        self._hash: t.Optional[int] = None
 104
 105        for arg_key, value in self.args.items():
 106            self._set_parent(arg_key, value)
 107
 108    def __eq__(self, other) -> bool:
 109        return type(self) is type(other) and hash(self) == hash(other)
 110
 111    @property
 112    def hashable_args(self) -> t.Any:
 113        return frozenset(
 114            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 115            for k, v in self.args.items()
 116            if not (v is None or v is False or (type(v) is list and not v))
 117        )
 118
 119    def __hash__(self) -> int:
 120        if self._hash is not None:
 121            return self._hash
 122
 123        return hash((self.__class__, self.hashable_args))
 124
 125    @property
 126    def this(self) -> t.Any:
 127        """
 128        Retrieves the argument with key "this".
 129        """
 130        return self.args.get("this")
 131
 132    @property
 133    def expression(self) -> t.Any:
 134        """
 135        Retrieves the argument with key "expression".
 136        """
 137        return self.args.get("expression")
 138
 139    @property
 140    def expressions(self) -> t.List[t.Any]:
 141        """
 142        Retrieves the argument with key "expressions".
 143        """
 144        return self.args.get("expressions") or []
 145
 146    def text(self, key) -> str:
 147        """
 148        Returns a textual representation of the argument corresponding to "key". This can only be used
 149        for args that are strings or leaf Expression instances, such as identifiers and literals.
 150        """
 151        field = self.args.get(key)
 152        if isinstance(field, str):
 153            return field
 154        if isinstance(field, (Identifier, Literal, Var)):
 155            return field.this
 156        if isinstance(field, (Star, Null)):
 157            return field.name
 158        return ""
 159
 160    @property
 161    def is_string(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a string.
 164        """
 165        return isinstance(self, Literal) and self.args["is_string"]
 166
 167    @property
 168    def is_number(self) -> bool:
 169        """
 170        Checks whether a Literal expression is a number.
 171        """
 172        return isinstance(self, Literal) and not self.args["is_string"]
 173
 174    @property
 175    def is_int(self) -> bool:
 176        """
 177        Checks whether a Literal expression is an integer.
 178        """
 179        return self.is_number and is_int(self.name)
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    def is_type(self, *dtypes) -> bool:
 239        return self.type is not None and self.type.is_type(*dtypes)
 240
 241    def is_leaf(self) -> bool:
 242        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        root = self.__class__()
 252        stack = [(self, root)]
 253
 254        while stack:
 255            node, copy = stack.pop()
 256
 257            if node.comments is not None:
 258                copy.comments = deepcopy(node.comments)
 259            if node._type is not None:
 260                copy._type = deepcopy(node._type)
 261            if node._meta is not None:
 262                copy._meta = deepcopy(node._meta)
 263            if node._hash is not None:
 264                copy._hash = node._hash
 265
 266            for k, vs in node.args.items():
 267                if hasattr(vs, "parent"):
 268                    stack.append((vs, vs.__class__()))
 269                    copy.set(k, stack[-1][-1])
 270                elif type(vs) is list:
 271                    copy.args[k] = []
 272
 273                    for v in vs:
 274                        if hasattr(v, "parent"):
 275                            stack.append((v, v.__class__()))
 276                            copy.append(k, stack[-1][-1])
 277                        else:
 278                            copy.append(k, v)
 279                else:
 280                    copy.args[k] = vs
 281
 282        return root
 283
 284    def copy(self):
 285        """
 286        Returns a deep copy of the expression.
 287        """
 288        new = deepcopy(self)
 289        new.parent = self.parent
 290        return new
 291
 292    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 293        if self.comments is None:
 294            self.comments = []
 295        if comments:
 296            for comment in comments:
 297                _, *meta = comment.split(SQLGLOT_META)
 298                if meta:
 299                    for kv in "".join(meta).split(","):
 300                        k, *v = kv.split("=")
 301                        value = v[0].strip() if v else True
 302                        self.meta[k.strip()] = value
 303                self.comments.append(comment)
 304
 305    def append(self, arg_key: str, value: t.Any) -> None:
 306        """
 307        Appends value to arg_key if it's a list or sets it as a new list.
 308
 309        Args:
 310            arg_key (str): name of the list expression arg
 311            value (Any): value to append to the list
 312        """
 313        if type(self.args.get(arg_key)) is not list:
 314            self.args[arg_key] = []
 315        self.args[arg_key].append(value)
 316        self._set_parent(arg_key, value)
 317
 318    def set(self, arg_key: str, value: t.Any) -> None:
 319        """
 320        Sets arg_key to value.
 321
 322        Args:
 323            arg_key: name of the expression arg.
 324            value: value to set the arg to.
 325        """
 326        if value is None:
 327            self.args.pop(arg_key, None)
 328            return
 329
 330        self.args[arg_key] = value
 331        self._set_parent(arg_key, value)
 332
 333    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 334        if hasattr(value, "parent"):
 335            value.parent = self
 336            value.arg_key = arg_key
 337        elif type(value) is list:
 338            for v in value:
 339                if hasattr(v, "parent"):
 340                    v.parent = self
 341                    v.arg_key = arg_key
 342
 343    @property
 344    def depth(self) -> int:
 345        """
 346        Returns the depth of this tree.
 347        """
 348        if self.parent:
 349            return self.parent.depth + 1
 350        return 0
 351
 352    def iter_expressions(self, reverse: bool = False) -> t.Iterator[t.Tuple[str, Expression]]:
 353        """Yields the key and expression for all arguments, exploding list args."""
 354        # need to materialize tuple due to python 3.7
 355        for k, vs in reversed(tuple(self.args.items())) if reverse else self.args.items():
 356            if type(vs) is list:
 357                for v in reversed(vs) if reverse else vs:
 358                    if hasattr(v, "parent"):
 359                        yield k, v
 360            else:
 361                if hasattr(vs, "parent"):
 362                    yield k, vs
 363
 364    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 365        """
 366        Returns the first node in this tree which matches at least one of
 367        the specified types.
 368
 369        Args:
 370            expression_types: the expression type(s) to match.
 371            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 372
 373        Returns:
 374            The node which matches the criteria or None if no such node was found.
 375        """
 376        return next(self.find_all(*expression_types, bfs=bfs), None)
 377
 378    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 379        """
 380        Returns a generator object which visits all nodes in this tree and only
 381        yields those that match at least one of the specified expression types.
 382
 383        Args:
 384            expression_types: the expression type(s) to match.
 385            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 386
 387        Returns:
 388            The generator object.
 389        """
 390        for expression, *_ in self.walk(bfs=bfs):
 391            if isinstance(expression, expression_types):
 392                yield expression
 393
 394    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 395        """
 396        Returns a nearest parent matching expression_types.
 397
 398        Args:
 399            expression_types: the expression type(s) to match.
 400
 401        Returns:
 402            The parent node.
 403        """
 404        ancestor = self.parent
 405        while ancestor and not isinstance(ancestor, expression_types):
 406            ancestor = ancestor.parent
 407        return ancestor  # type: ignore
 408
 409    @property
 410    def parent_select(self) -> t.Optional[Select]:
 411        """
 412        Returns the parent select statement.
 413        """
 414        return self.find_ancestor(Select)
 415
 416    @property
 417    def same_parent(self) -> bool:
 418        """Returns if the parent is the same class as itself."""
 419        return type(self.parent) is self.__class__
 420
 421    def root(self) -> Expression:
 422        """
 423        Returns the root expression of this tree.
 424        """
 425        expression = self
 426        while expression.parent:
 427            expression = expression.parent
 428        return expression
 429
 430    def walk(self, bfs=True, prune=None):
 431        """
 432        Returns a generator object which visits all nodes in this tree.
 433
 434        Args:
 435            bfs (bool): if set to True the BFS traversal order will be applied,
 436                otherwise the DFS traversal will be used instead.
 437            prune ((node, parent, arg_key) -> bool): callable that returns True if
 438                the generator should stop traversing this branch of the tree.
 439
 440        Returns:
 441            the generator object.
 442        """
 443        if bfs:
 444            yield from self.bfs(prune=prune)
 445        else:
 446            yield from self.dfs(prune=prune)
 447
 448    def dfs(self, parent=None, key=None, prune=None):
 449        """
 450        Returns a generator object which visits all nodes in this tree in
 451        the DFS (Depth-first) order.
 452
 453        Returns:
 454            The generator object.
 455        """
 456        stack = [(self, parent or self.parent, key)]
 457
 458        while stack:
 459            node, parent, key = stack.pop()
 460
 461            yield node, parent, key
 462
 463            if prune and prune(node, parent, key):
 464                continue
 465
 466            for k, v in node.iter_expressions(reverse=True):
 467                stack.append((v, node, k))
 468
 469    def bfs(self, prune=None):
 470        """
 471        Returns a generator object which visits all nodes in this tree in
 472        the BFS (Breadth-first) order.
 473
 474        Returns:
 475            The generator object.
 476        """
 477        queue = deque([(self, self.parent, None)])
 478
 479        while queue:
 480            item, parent, key = queue.popleft()
 481
 482            yield item, parent, key
 483            if prune and prune(item, parent, key):
 484                continue
 485
 486            for k, v in item.iter_expressions():
 487                queue.append((v, item, k))
 488
 489    def unnest(self):
 490        """
 491        Returns the first non parenthesis child or self.
 492        """
 493        expression = self
 494        while type(expression) is Paren:
 495            expression = expression.this
 496        return expression
 497
 498    def unalias(self):
 499        """
 500        Returns the inner expression if this is an Alias.
 501        """
 502        if isinstance(self, Alias):
 503            return self.this
 504        return self
 505
 506    def unnest_operands(self):
 507        """
 508        Returns unnested operands as a tuple.
 509        """
 510        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 511
 512    def flatten(self, unnest=True):
 513        """
 514        Returns a generator which yields child nodes whose parents are the same class.
 515
 516        A AND B AND C -> [A, B, C]
 517        """
 518        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and type(n) is not self.__class__):
 519            if type(node) is not self.__class__:
 520                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 521
 522    def __str__(self) -> str:
 523        return self.sql()
 524
 525    def __repr__(self) -> str:
 526        return _to_s(self)
 527
 528    def to_s(self) -> str:
 529        """
 530        Same as __repr__, but includes additional information which can be useful
 531        for debugging, like empty or missing args and the AST nodes' object IDs.
 532        """
 533        return _to_s(self, verbose=True)
 534
 535    def sql(self, dialect: DialectType = None, **opts) -> str:
 536        """
 537        Returns SQL string representation of this tree.
 538
 539        Args:
 540            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 541            opts: other `sqlglot.generator.Generator` options.
 542
 543        Returns:
 544            The SQL string.
 545        """
 546        from sqlglot.dialects import Dialect
 547
 548        return Dialect.get_or_raise(dialect).generate(self, **opts)
 549
 550    def transform(self, fun, *args, copy=True, **kwargs):
 551        """
 552        Recursively visits all tree nodes (excluding already transformed ones)
 553        and applies the given transformation function to each node.
 554
 555        Args:
 556            fun (function): a function which takes a node as an argument and returns a
 557                new transformed node or the same node without modifications. If the function
 558                returns None, then the corresponding node will be removed from the syntax tree.
 559            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 560                modified in place.
 561
 562        Returns:
 563            The transformed tree.
 564        """
 565        node = self.copy() if copy else self
 566        new_node = fun(node, *args, **kwargs)
 567
 568        if new_node is None or not isinstance(new_node, Expression):
 569            return new_node
 570        if new_node is not node:
 571            new_node.parent = node.parent
 572            return new_node
 573
 574        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 575        return new_node
 576
 577    @t.overload
 578    def replace(self, expression: E) -> E: ...
 579
 580    @t.overload
 581    def replace(self, expression: None) -> None: ...
 582
 583    def replace(self, expression):
 584        """
 585        Swap out this expression with a new expression.
 586
 587        For example::
 588
 589            >>> tree = Select().select("x").from_("tbl")
 590            >>> tree.find(Column).replace(column("y"))
 591            Column(
 592              this=Identifier(this=y, quoted=False))
 593            >>> tree.sql()
 594            'SELECT y FROM tbl'
 595
 596        Args:
 597            expression: new node
 598
 599        Returns:
 600            The new expression or expressions.
 601        """
 602        if not self.parent:
 603            return expression
 604
 605        parent = self.parent
 606        self.parent = None
 607
 608        replace_children(parent, lambda child: expression if child is self else child)
 609        return expression
 610
 611    def pop(self: E) -> E:
 612        """
 613        Remove this expression from its AST.
 614
 615        Returns:
 616            The popped expression.
 617        """
 618        self.replace(None)
 619        return self
 620
 621    def assert_is(self, type_: t.Type[E]) -> E:
 622        """
 623        Assert that this `Expression` is an instance of `type_`.
 624
 625        If it is NOT an instance of `type_`, this raises an assertion error.
 626        Otherwise, this returns this expression.
 627
 628        Examples:
 629            This is useful for type security in chained expressions:
 630
 631            >>> import sqlglot
 632            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 633            'SELECT x, z FROM y'
 634        """
 635        if not isinstance(self, type_):
 636            raise AssertionError(f"{self} is not {type_}.")
 637        return self
 638
 639    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 640        """
 641        Checks if this expression is valid (e.g. all mandatory args are set).
 642
 643        Args:
 644            args: a sequence of values that were used to instantiate a Func expression. This is used
 645                to check that the provided arguments don't exceed the function argument limit.
 646
 647        Returns:
 648            A list of error messages for all possible errors that were found.
 649        """
 650        errors: t.List[str] = []
 651
 652        for k in self.args:
 653            if k not in self.arg_types:
 654                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 655        for k, mandatory in self.arg_types.items():
 656            v = self.args.get(k)
 657            if mandatory and (v is None or (isinstance(v, list) and not v)):
 658                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 659
 660        if (
 661            args
 662            and isinstance(self, Func)
 663            and len(args) > len(self.arg_types)
 664            and not self.is_var_len_args
 665        ):
 666            errors.append(
 667                f"The number of provided arguments ({len(args)}) is greater than "
 668                f"the maximum number of supported arguments ({len(self.arg_types)})"
 669            )
 670
 671        return errors
 672
 673    def dump(self):
 674        """
 675        Dump this Expression to a JSON-serializable dict.
 676        """
 677        from sqlglot.serde import dump
 678
 679        return dump(self)
 680
 681    @classmethod
 682    def load(cls, obj):
 683        """
 684        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 685        """
 686        from sqlglot.serde import load
 687
 688        return load(obj)
 689
 690    def and_(
 691        self,
 692        *expressions: t.Optional[ExpOrStr],
 693        dialect: DialectType = None,
 694        copy: bool = True,
 695        **opts,
 696    ) -> Condition:
 697        """
 698        AND this condition with one or multiple expressions.
 699
 700        Example:
 701            >>> condition("x=1").and_("y=1").sql()
 702            'x = 1 AND y = 1'
 703
 704        Args:
 705            *expressions: the SQL code strings to parse.
 706                If an `Expression` instance is passed, it will be used as-is.
 707            dialect: the dialect used to parse the input expression.
 708            copy: whether to copy the involved expressions (only applies to Expressions).
 709            opts: other options to use to parse the input expressions.
 710
 711        Returns:
 712            The new And condition.
 713        """
 714        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 715
 716    def or_(
 717        self,
 718        *expressions: t.Optional[ExpOrStr],
 719        dialect: DialectType = None,
 720        copy: bool = True,
 721        **opts,
 722    ) -> Condition:
 723        """
 724        OR this condition with one or multiple expressions.
 725
 726        Example:
 727            >>> condition("x=1").or_("y=1").sql()
 728            'x = 1 OR y = 1'
 729
 730        Args:
 731            *expressions: the SQL code strings to parse.
 732                If an `Expression` instance is passed, it will be used as-is.
 733            dialect: the dialect used to parse the input expression.
 734            copy: whether to copy the involved expressions (only applies to Expressions).
 735            opts: other options to use to parse the input expressions.
 736
 737        Returns:
 738            The new Or condition.
 739        """
 740        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 741
 742    def not_(self, copy: bool = True):
 743        """
 744        Wrap this condition with NOT.
 745
 746        Example:
 747            >>> condition("x=1").not_().sql()
 748            'NOT x = 1'
 749
 750        Args:
 751            copy: whether to copy this object.
 752
 753        Returns:
 754            The new Not instance.
 755        """
 756        return not_(self, copy=copy)
 757
 758    def as_(
 759        self,
 760        alias: str | Identifier,
 761        quoted: t.Optional[bool] = None,
 762        dialect: DialectType = None,
 763        copy: bool = True,
 764        **opts,
 765    ) -> Alias:
 766        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 767
 768    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 769        this = self.copy()
 770        other = convert(other, copy=True)
 771        if not isinstance(this, klass) and not isinstance(other, klass):
 772            this = _wrap(this, Binary)
 773            other = _wrap(other, Binary)
 774        if reverse:
 775            return klass(this=other, expression=this)
 776        return klass(this=this, expression=other)
 777
 778    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 779        return Bracket(
 780            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 781        )
 782
 783    def __iter__(self) -> t.Iterator:
 784        if "expressions" in self.arg_types:
 785            return iter(self.args.get("expressions") or [])
 786        # We define this because __getitem__ converts Expression into an iterable, which is
 787        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 788        # See: https://peps.python.org/pep-0234/
 789        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 790
 791    def isin(
 792        self,
 793        *expressions: t.Any,
 794        query: t.Optional[ExpOrStr] = None,
 795        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 796        copy: bool = True,
 797        **opts,
 798    ) -> In:
 799        return In(
 800            this=maybe_copy(self, copy),
 801            expressions=[convert(e, copy=copy) for e in expressions],
 802            query=maybe_parse(query, copy=copy, **opts) if query else None,
 803            unnest=(
 804                Unnest(
 805                    expressions=[
 806                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 807                        for e in ensure_list(unnest)
 808                    ]
 809                )
 810                if unnest
 811                else None
 812            ),
 813        )
 814
 815    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 816        return Between(
 817            this=maybe_copy(self, copy),
 818            low=convert(low, copy=copy, **opts),
 819            high=convert(high, copy=copy, **opts),
 820        )
 821
 822    def is_(self, other: ExpOrStr) -> Is:
 823        return self._binop(Is, other)
 824
 825    def like(self, other: ExpOrStr) -> Like:
 826        return self._binop(Like, other)
 827
 828    def ilike(self, other: ExpOrStr) -> ILike:
 829        return self._binop(ILike, other)
 830
 831    def eq(self, other: t.Any) -> EQ:
 832        return self._binop(EQ, other)
 833
 834    def neq(self, other: t.Any) -> NEQ:
 835        return self._binop(NEQ, other)
 836
 837    def rlike(self, other: ExpOrStr) -> RegexpLike:
 838        return self._binop(RegexpLike, other)
 839
 840    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 841        div = self._binop(Div, other)
 842        div.args["typed"] = typed
 843        div.args["safe"] = safe
 844        return div
 845
 846    def desc(self, nulls_first: bool = False) -> Ordered:
 847        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 848
 849    def __lt__(self, other: t.Any) -> LT:
 850        return self._binop(LT, other)
 851
 852    def __le__(self, other: t.Any) -> LTE:
 853        return self._binop(LTE, other)
 854
 855    def __gt__(self, other: t.Any) -> GT:
 856        return self._binop(GT, other)
 857
 858    def __ge__(self, other: t.Any) -> GTE:
 859        return self._binop(GTE, other)
 860
 861    def __add__(self, other: t.Any) -> Add:
 862        return self._binop(Add, other)
 863
 864    def __radd__(self, other: t.Any) -> Add:
 865        return self._binop(Add, other, reverse=True)
 866
 867    def __sub__(self, other: t.Any) -> Sub:
 868        return self._binop(Sub, other)
 869
 870    def __rsub__(self, other: t.Any) -> Sub:
 871        return self._binop(Sub, other, reverse=True)
 872
 873    def __mul__(self, other: t.Any) -> Mul:
 874        return self._binop(Mul, other)
 875
 876    def __rmul__(self, other: t.Any) -> Mul:
 877        return self._binop(Mul, other, reverse=True)
 878
 879    def __truediv__(self, other: t.Any) -> Div:
 880        return self._binop(Div, other)
 881
 882    def __rtruediv__(self, other: t.Any) -> Div:
 883        return self._binop(Div, other, reverse=True)
 884
 885    def __floordiv__(self, other: t.Any) -> IntDiv:
 886        return self._binop(IntDiv, other)
 887
 888    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 889        return self._binop(IntDiv, other, reverse=True)
 890
 891    def __mod__(self, other: t.Any) -> Mod:
 892        return self._binop(Mod, other)
 893
 894    def __rmod__(self, other: t.Any) -> Mod:
 895        return self._binop(Mod, other, reverse=True)
 896
 897    def __pow__(self, other: t.Any) -> Pow:
 898        return self._binop(Pow, other)
 899
 900    def __rpow__(self, other: t.Any) -> Pow:
 901        return self._binop(Pow, other, reverse=True)
 902
 903    def __and__(self, other: t.Any) -> And:
 904        return self._binop(And, other)
 905
 906    def __rand__(self, other: t.Any) -> And:
 907        return self._binop(And, other, reverse=True)
 908
 909    def __or__(self, other: t.Any) -> Or:
 910        return self._binop(Or, other)
 911
 912    def __ror__(self, other: t.Any) -> Or:
 913        return self._binop(Or, other, reverse=True)
 914
 915    def __neg__(self) -> Neg:
 916        return Neg(this=_wrap(self.copy(), Binary))
 917
 918    def __invert__(self) -> Not:
 919        return not_(self.copy())
 920
 921
 922IntoType = t.Union[
 923    str,
 924    t.Type[Expression],
 925    t.Collection[t.Union[str, t.Type[Expression]]],
 926]
 927ExpOrStr = t.Union[str, Expression]
 928
 929
 930class Condition(Expression):
 931    """Logical conditions like x AND y, or simply x"""
 932
 933
 934class Predicate(Condition):
 935    """Relationships like x = y, x > 1, x >= y."""
 936
 937
 938class DerivedTable(Expression):
 939    @property
 940    def selects(self) -> t.List[Expression]:
 941        return self.this.selects if isinstance(self.this, Query) else []
 942
 943    @property
 944    def named_selects(self) -> t.List[str]:
 945        return [select.output_name for select in self.selects]
 946
 947
 948class Query(Expression):
 949    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
 950        """
 951        Returns a `Subquery` that wraps around this query.
 952
 953        Example:
 954            >>> subquery = Select().select("x").from_("tbl").subquery()
 955            >>> Select().select("x").from_(subquery).sql()
 956            'SELECT x FROM (SELECT x FROM tbl)'
 957
 958        Args:
 959            alias: an optional alias for the subquery.
 960            copy: if `False`, modify this expression instance in-place.
 961        """
 962        instance = maybe_copy(self, copy)
 963        if not isinstance(alias, Expression):
 964            alias = TableAlias(this=to_identifier(alias)) if alias else None
 965
 966        return Subquery(this=instance, alias=alias)
 967
 968    def limit(
 969        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
 970    ) -> Select:
 971        """
 972        Adds a LIMIT clause to this query.
 973
 974        Example:
 975            >>> select("1").union(select("1")).limit(1).sql()
 976            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
 977
 978        Args:
 979            expression: the SQL code string to parse.
 980                This can also be an integer.
 981                If a `Limit` instance is passed, it will be used as-is.
 982                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
 983            dialect: the dialect used to parse the input expression.
 984            copy: if `False`, modify this expression instance in-place.
 985            opts: other options to use to parse the input expressions.
 986
 987        Returns:
 988            A limited Select expression.
 989        """
 990        return (
 991            select("*")
 992            .from_(self.subquery(alias="_l_0", copy=copy))
 993            .limit(expression, dialect=dialect, copy=False, **opts)
 994        )
 995
 996    @property
 997    def ctes(self) -> t.List[CTE]:
 998        """Returns a list of all the CTEs attached to this query."""
 999        with_ = self.args.get("with")
1000        return with_.expressions if with_ else []
1001
1002    @property
1003    def selects(self) -> t.List[Expression]:
1004        """Returns the query's projections."""
1005        raise NotImplementedError("Query objects must implement `selects`")
1006
1007    @property
1008    def named_selects(self) -> t.List[str]:
1009        """Returns the output names of the query's projections."""
1010        raise NotImplementedError("Query objects must implement `named_selects`")
1011
1012    def select(
1013        self,
1014        *expressions: t.Optional[ExpOrStr],
1015        append: bool = True,
1016        dialect: DialectType = None,
1017        copy: bool = True,
1018        **opts,
1019    ) -> Query:
1020        """
1021        Append to or set the SELECT expressions.
1022
1023        Example:
1024            >>> Select().select("x", "y").sql()
1025            'SELECT x, y'
1026
1027        Args:
1028            *expressions: the SQL code strings to parse.
1029                If an `Expression` instance is passed, it will be used as-is.
1030            append: if `True`, add to any existing expressions.
1031                Otherwise, this resets the expressions.
1032            dialect: the dialect used to parse the input expressions.
1033            copy: if `False`, modify this expression instance in-place.
1034            opts: other options to use to parse the input expressions.
1035
1036        Returns:
1037            The modified Query expression.
1038        """
1039        raise NotImplementedError("Query objects must implement `select`")
1040
1041    def with_(
1042        self,
1043        alias: ExpOrStr,
1044        as_: ExpOrStr,
1045        recursive: t.Optional[bool] = None,
1046        append: bool = True,
1047        dialect: DialectType = None,
1048        copy: bool = True,
1049        **opts,
1050    ) -> Query:
1051        """
1052        Append to or set the common table expressions.
1053
1054        Example:
1055            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1056            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1057
1058        Args:
1059            alias: the SQL code string to parse as the table name.
1060                If an `Expression` instance is passed, this is used as-is.
1061            as_: the SQL code string to parse as the table expression.
1062                If an `Expression` instance is passed, it will be used as-is.
1063            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1064            append: if `True`, add to any existing expressions.
1065                Otherwise, this resets the expressions.
1066            dialect: the dialect used to parse the input expression.
1067            copy: if `False`, modify this expression instance in-place.
1068            opts: other options to use to parse the input expressions.
1069
1070        Returns:
1071            The modified expression.
1072        """
1073        return _apply_cte_builder(
1074            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1075        )
1076
1077    def union(
1078        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1079    ) -> Union:
1080        """
1081        Builds a UNION expression.
1082
1083        Example:
1084            >>> import sqlglot
1085            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1086            'SELECT * FROM foo UNION SELECT * FROM bla'
1087
1088        Args:
1089            expression: the SQL code string.
1090                If an `Expression` instance is passed, it will be used as-is.
1091            distinct: set the DISTINCT flag if and only if this is true.
1092            dialect: the dialect used to parse the input expression.
1093            opts: other options to use to parse the input expressions.
1094
1095        Returns:
1096            The new Union expression.
1097        """
1098        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1099
1100    def intersect(
1101        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1102    ) -> Intersect:
1103        """
1104        Builds an INTERSECT expression.
1105
1106        Example:
1107            >>> import sqlglot
1108            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1109            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1110
1111        Args:
1112            expression: the SQL code string.
1113                If an `Expression` instance is passed, it will be used as-is.
1114            distinct: set the DISTINCT flag if and only if this is true.
1115            dialect: the dialect used to parse the input expression.
1116            opts: other options to use to parse the input expressions.
1117
1118        Returns:
1119            The new Intersect expression.
1120        """
1121        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1122
1123    def except_(
1124        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1125    ) -> Except:
1126        """
1127        Builds an EXCEPT expression.
1128
1129        Example:
1130            >>> import sqlglot
1131            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1132            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1133
1134        Args:
1135            expression: the SQL code string.
1136                If an `Expression` instance is passed, it will be used as-is.
1137            distinct: set the DISTINCT flag if and only if this is true.
1138            dialect: the dialect used to parse the input expression.
1139            opts: other options to use to parse the input expressions.
1140
1141        Returns:
1142            The new Except expression.
1143        """
1144        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1145
1146
1147class UDTF(DerivedTable):
1148    @property
1149    def selects(self) -> t.List[Expression]:
1150        alias = self.args.get("alias")
1151        return alias.columns if alias else []
1152
1153
1154class Cache(Expression):
1155    arg_types = {
1156        "this": True,
1157        "lazy": False,
1158        "options": False,
1159        "expression": False,
1160    }
1161
1162
1163class Uncache(Expression):
1164    arg_types = {"this": True, "exists": False}
1165
1166
1167class Refresh(Expression):
1168    pass
1169
1170
1171class DDL(Expression):
1172    @property
1173    def ctes(self) -> t.List[CTE]:
1174        """Returns a list of all the CTEs attached to this statement."""
1175        with_ = self.args.get("with")
1176        return with_.expressions if with_ else []
1177
1178    @property
1179    def selects(self) -> t.List[Expression]:
1180        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1181        return self.expression.selects if isinstance(self.expression, Query) else []
1182
1183    @property
1184    def named_selects(self) -> t.List[str]:
1185        """
1186        If this statement contains a query (e.g. a CTAS), this returns the output
1187        names of the query's projections.
1188        """
1189        return self.expression.named_selects if isinstance(self.expression, Query) else []
1190
1191
1192class DML(Expression):
1193    def returning(
1194        self,
1195        expression: ExpOrStr,
1196        dialect: DialectType = None,
1197        copy: bool = True,
1198        **opts,
1199    ) -> DML:
1200        """
1201        Set the RETURNING expression. Not supported by all dialects.
1202
1203        Example:
1204            >>> delete("tbl").returning("*", dialect="postgres").sql()
1205            'DELETE FROM tbl RETURNING *'
1206
1207        Args:
1208            expression: the SQL code strings to parse.
1209                If an `Expression` instance is passed, it will be used as-is.
1210            dialect: the dialect used to parse the input expressions.
1211            copy: if `False`, modify this expression instance in-place.
1212            opts: other options to use to parse the input expressions.
1213
1214        Returns:
1215            Delete: the modified expression.
1216        """
1217        return _apply_builder(
1218            expression=expression,
1219            instance=self,
1220            arg="returning",
1221            prefix="RETURNING",
1222            dialect=dialect,
1223            copy=copy,
1224            into=Returning,
1225            **opts,
1226        )
1227
1228
1229class Create(DDL):
1230    arg_types = {
1231        "with": False,
1232        "this": True,
1233        "kind": True,
1234        "expression": False,
1235        "exists": False,
1236        "properties": False,
1237        "replace": False,
1238        "unique": False,
1239        "indexes": False,
1240        "no_schema_binding": False,
1241        "begin": False,
1242        "end": False,
1243        "clone": False,
1244    }
1245
1246    @property
1247    def kind(self) -> t.Optional[str]:
1248        kind = self.args.get("kind")
1249        return kind and kind.upper()
1250
1251
1252class SequenceProperties(Expression):
1253    arg_types = {
1254        "increment": False,
1255        "minvalue": False,
1256        "maxvalue": False,
1257        "cache": False,
1258        "start": False,
1259        "owned": False,
1260        "options": False,
1261    }
1262
1263
1264class TruncateTable(Expression):
1265    arg_types = {
1266        "expressions": True,
1267        "is_database": False,
1268        "exists": False,
1269        "only": False,
1270        "cluster": False,
1271        "identity": False,
1272        "option": False,
1273        "partition": False,
1274    }
1275
1276
1277# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1278# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1279# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1280class Clone(Expression):
1281    arg_types = {"this": True, "shallow": False, "copy": False}
1282
1283
1284class Describe(Expression):
1285    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1286
1287
1288class Kill(Expression):
1289    arg_types = {"this": True, "kind": False}
1290
1291
1292class Pragma(Expression):
1293    pass
1294
1295
1296class Set(Expression):
1297    arg_types = {"expressions": False, "unset": False, "tag": False}
1298
1299
1300class Heredoc(Expression):
1301    arg_types = {"this": True, "tag": False}
1302
1303
1304class SetItem(Expression):
1305    arg_types = {
1306        "this": False,
1307        "expressions": False,
1308        "kind": False,
1309        "collate": False,  # MySQL SET NAMES statement
1310        "global": False,
1311    }
1312
1313
1314class Show(Expression):
1315    arg_types = {
1316        "this": True,
1317        "history": False,
1318        "terse": False,
1319        "target": False,
1320        "offset": False,
1321        "starts_with": False,
1322        "limit": False,
1323        "from": False,
1324        "like": False,
1325        "where": False,
1326        "db": False,
1327        "scope": False,
1328        "scope_kind": False,
1329        "full": False,
1330        "mutex": False,
1331        "query": False,
1332        "channel": False,
1333        "global": False,
1334        "log": False,
1335        "position": False,
1336        "types": False,
1337    }
1338
1339
1340class UserDefinedFunction(Expression):
1341    arg_types = {"this": True, "expressions": False, "wrapped": False}
1342
1343
1344class CharacterSet(Expression):
1345    arg_types = {"this": True, "default": False}
1346
1347
1348class With(Expression):
1349    arg_types = {"expressions": True, "recursive": False}
1350
1351    @property
1352    def recursive(self) -> bool:
1353        return bool(self.args.get("recursive"))
1354
1355
1356class WithinGroup(Expression):
1357    arg_types = {"this": True, "expression": False}
1358
1359
1360# clickhouse supports scalar ctes
1361# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1362class CTE(DerivedTable):
1363    arg_types = {"this": True, "alias": True, "scalar": False}
1364
1365
1366class TableAlias(Expression):
1367    arg_types = {"this": False, "columns": False}
1368
1369    @property
1370    def columns(self):
1371        return self.args.get("columns") or []
1372
1373
1374class BitString(Condition):
1375    pass
1376
1377
1378class HexString(Condition):
1379    pass
1380
1381
1382class ByteString(Condition):
1383    pass
1384
1385
1386class RawString(Condition):
1387    pass
1388
1389
1390class UnicodeString(Condition):
1391    arg_types = {"this": True, "escape": False}
1392
1393
1394class Column(Condition):
1395    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1396
1397    @property
1398    def table(self) -> str:
1399        return self.text("table")
1400
1401    @property
1402    def db(self) -> str:
1403        return self.text("db")
1404
1405    @property
1406    def catalog(self) -> str:
1407        return self.text("catalog")
1408
1409    @property
1410    def output_name(self) -> str:
1411        return self.name
1412
1413    @property
1414    def parts(self) -> t.List[Identifier]:
1415        """Return the parts of a column in order catalog, db, table, name."""
1416        return [
1417            t.cast(Identifier, self.args[part])
1418            for part in ("catalog", "db", "table", "this")
1419            if self.args.get(part)
1420        ]
1421
1422    def to_dot(self) -> Dot | Identifier:
1423        """Converts the column into a dot expression."""
1424        parts = self.parts
1425        parent = self.parent
1426
1427        while parent:
1428            if isinstance(parent, Dot):
1429                parts.append(parent.expression)
1430            parent = parent.parent
1431
1432        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1433
1434
1435class ColumnPosition(Expression):
1436    arg_types = {"this": False, "position": True}
1437
1438
1439class ColumnDef(Expression):
1440    arg_types = {
1441        "this": True,
1442        "kind": False,
1443        "constraints": False,
1444        "exists": False,
1445        "position": False,
1446    }
1447
1448    @property
1449    def constraints(self) -> t.List[ColumnConstraint]:
1450        return self.args.get("constraints") or []
1451
1452    @property
1453    def kind(self) -> t.Optional[DataType]:
1454        return self.args.get("kind")
1455
1456
1457class AlterColumn(Expression):
1458    arg_types = {
1459        "this": True,
1460        "dtype": False,
1461        "collate": False,
1462        "using": False,
1463        "default": False,
1464        "drop": False,
1465        "comment": False,
1466    }
1467
1468
1469class RenameColumn(Expression):
1470    arg_types = {"this": True, "to": True, "exists": False}
1471
1472
1473class RenameTable(Expression):
1474    pass
1475
1476
1477class SwapTable(Expression):
1478    pass
1479
1480
1481class Comment(Expression):
1482    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1483
1484
1485class Comprehension(Expression):
1486    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1487
1488
1489# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1490class MergeTreeTTLAction(Expression):
1491    arg_types = {
1492        "this": True,
1493        "delete": False,
1494        "recompress": False,
1495        "to_disk": False,
1496        "to_volume": False,
1497    }
1498
1499
1500# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1501class MergeTreeTTL(Expression):
1502    arg_types = {
1503        "expressions": True,
1504        "where": False,
1505        "group": False,
1506        "aggregates": False,
1507    }
1508
1509
1510# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1511class IndexConstraintOption(Expression):
1512    arg_types = {
1513        "key_block_size": False,
1514        "using": False,
1515        "parser": False,
1516        "comment": False,
1517        "visible": False,
1518        "engine_attr": False,
1519        "secondary_engine_attr": False,
1520    }
1521
1522
1523class ColumnConstraint(Expression):
1524    arg_types = {"this": False, "kind": True}
1525
1526    @property
1527    def kind(self) -> ColumnConstraintKind:
1528        return self.args["kind"]
1529
1530
1531class ColumnConstraintKind(Expression):
1532    pass
1533
1534
1535class AutoIncrementColumnConstraint(ColumnConstraintKind):
1536    pass
1537
1538
1539class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1540    arg_types = {"this": True, "expression": True}
1541
1542
1543class CaseSpecificColumnConstraint(ColumnConstraintKind):
1544    arg_types = {"not_": True}
1545
1546
1547class CharacterSetColumnConstraint(ColumnConstraintKind):
1548    arg_types = {"this": True}
1549
1550
1551class CheckColumnConstraint(ColumnConstraintKind):
1552    arg_types = {"this": True, "enforced": False}
1553
1554
1555class ClusteredColumnConstraint(ColumnConstraintKind):
1556    pass
1557
1558
1559class CollateColumnConstraint(ColumnConstraintKind):
1560    pass
1561
1562
1563class CommentColumnConstraint(ColumnConstraintKind):
1564    pass
1565
1566
1567class CompressColumnConstraint(ColumnConstraintKind):
1568    pass
1569
1570
1571class DateFormatColumnConstraint(ColumnConstraintKind):
1572    arg_types = {"this": True}
1573
1574
1575class DefaultColumnConstraint(ColumnConstraintKind):
1576    pass
1577
1578
1579class EncodeColumnConstraint(ColumnConstraintKind):
1580    pass
1581
1582
1583class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1584    # this: True -> ALWAYS, this: False -> BY DEFAULT
1585    arg_types = {
1586        "this": False,
1587        "expression": False,
1588        "on_null": False,
1589        "start": False,
1590        "increment": False,
1591        "minvalue": False,
1592        "maxvalue": False,
1593        "cycle": False,
1594    }
1595
1596
1597class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1598    arg_types = {"start": False, "hidden": False}
1599
1600
1601# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1602class IndexColumnConstraint(ColumnConstraintKind):
1603    arg_types = {
1604        "this": False,
1605        "schema": True,
1606        "kind": False,
1607        "index_type": False,
1608        "options": False,
1609    }
1610
1611
1612class InlineLengthColumnConstraint(ColumnConstraintKind):
1613    pass
1614
1615
1616class NonClusteredColumnConstraint(ColumnConstraintKind):
1617    pass
1618
1619
1620class NotForReplicationColumnConstraint(ColumnConstraintKind):
1621    arg_types = {}
1622
1623
1624class NotNullColumnConstraint(ColumnConstraintKind):
1625    arg_types = {"allow_null": False}
1626
1627
1628# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1629class OnUpdateColumnConstraint(ColumnConstraintKind):
1630    pass
1631
1632
1633# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1634class TransformColumnConstraint(ColumnConstraintKind):
1635    pass
1636
1637
1638class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1639    arg_types = {"desc": False}
1640
1641
1642class TitleColumnConstraint(ColumnConstraintKind):
1643    pass
1644
1645
1646class UniqueColumnConstraint(ColumnConstraintKind):
1647    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1648
1649
1650class UppercaseColumnConstraint(ColumnConstraintKind):
1651    arg_types: t.Dict[str, t.Any] = {}
1652
1653
1654class PathColumnConstraint(ColumnConstraintKind):
1655    pass
1656
1657
1658# computed column expression
1659# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1660class ComputedColumnConstraint(ColumnConstraintKind):
1661    arg_types = {"this": True, "persisted": False, "not_null": False}
1662
1663
1664class Constraint(Expression):
1665    arg_types = {"this": True, "expressions": True}
1666
1667
1668class Delete(DML):
1669    arg_types = {
1670        "with": False,
1671        "this": False,
1672        "using": False,
1673        "where": False,
1674        "returning": False,
1675        "limit": False,
1676        "tables": False,  # Multiple-Table Syntax (MySQL)
1677    }
1678
1679    def delete(
1680        self,
1681        table: ExpOrStr,
1682        dialect: DialectType = None,
1683        copy: bool = True,
1684        **opts,
1685    ) -> Delete:
1686        """
1687        Create a DELETE expression or replace the table on an existing DELETE expression.
1688
1689        Example:
1690            >>> delete("tbl").sql()
1691            'DELETE FROM tbl'
1692
1693        Args:
1694            table: the table from which to delete.
1695            dialect: the dialect used to parse the input expression.
1696            copy: if `False`, modify this expression instance in-place.
1697            opts: other options to use to parse the input expressions.
1698
1699        Returns:
1700            Delete: the modified expression.
1701        """
1702        return _apply_builder(
1703            expression=table,
1704            instance=self,
1705            arg="this",
1706            dialect=dialect,
1707            into=Table,
1708            copy=copy,
1709            **opts,
1710        )
1711
1712    def where(
1713        self,
1714        *expressions: t.Optional[ExpOrStr],
1715        append: bool = True,
1716        dialect: DialectType = None,
1717        copy: bool = True,
1718        **opts,
1719    ) -> Delete:
1720        """
1721        Append to or set the WHERE expressions.
1722
1723        Example:
1724            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1725            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1726
1727        Args:
1728            *expressions: the SQL code strings to parse.
1729                If an `Expression` instance is passed, it will be used as-is.
1730                Multiple expressions are combined with an AND operator.
1731            append: if `True`, AND the new expressions to any existing expression.
1732                Otherwise, this resets the expression.
1733            dialect: the dialect used to parse the input expressions.
1734            copy: if `False`, modify this expression instance in-place.
1735            opts: other options to use to parse the input expressions.
1736
1737        Returns:
1738            Delete: the modified expression.
1739        """
1740        return _apply_conjunction_builder(
1741            *expressions,
1742            instance=self,
1743            arg="where",
1744            append=append,
1745            into=Where,
1746            dialect=dialect,
1747            copy=copy,
1748            **opts,
1749        )
1750
1751
1752class Drop(Expression):
1753    arg_types = {
1754        "this": False,
1755        "kind": False,
1756        "exists": False,
1757        "temporary": False,
1758        "materialized": False,
1759        "cascade": False,
1760        "constraints": False,
1761        "purge": False,
1762    }
1763
1764
1765class Filter(Expression):
1766    arg_types = {"this": True, "expression": True}
1767
1768
1769class Check(Expression):
1770    pass
1771
1772
1773# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1774class Connect(Expression):
1775    arg_types = {"start": False, "connect": True}
1776
1777
1778class Prior(Expression):
1779    pass
1780
1781
1782class Directory(Expression):
1783    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1784    arg_types = {"this": True, "local": False, "row_format": False}
1785
1786
1787class ForeignKey(Expression):
1788    arg_types = {
1789        "expressions": True,
1790        "reference": False,
1791        "delete": False,
1792        "update": False,
1793    }
1794
1795
1796class ColumnPrefix(Expression):
1797    arg_types = {"this": True, "expression": True}
1798
1799
1800class PrimaryKey(Expression):
1801    arg_types = {"expressions": True, "options": False}
1802
1803
1804# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1805# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1806class Into(Expression):
1807    arg_types = {"this": True, "temporary": False, "unlogged": False}
1808
1809
1810class From(Expression):
1811    @property
1812    def name(self) -> str:
1813        return self.this.name
1814
1815    @property
1816    def alias_or_name(self) -> str:
1817        return self.this.alias_or_name
1818
1819
1820class Having(Expression):
1821    pass
1822
1823
1824class Hint(Expression):
1825    arg_types = {"expressions": True}
1826
1827
1828class JoinHint(Expression):
1829    arg_types = {"this": True, "expressions": True}
1830
1831
1832class Identifier(Expression):
1833    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1834
1835    @property
1836    def quoted(self) -> bool:
1837        return bool(self.args.get("quoted"))
1838
1839    @property
1840    def hashable_args(self) -> t.Any:
1841        return (self.this, self.quoted)
1842
1843    @property
1844    def output_name(self) -> str:
1845        return self.name
1846
1847
1848# https://www.postgresql.org/docs/current/indexes-opclass.html
1849class Opclass(Expression):
1850    arg_types = {"this": True, "expression": True}
1851
1852
1853class Index(Expression):
1854    arg_types = {
1855        "this": False,
1856        "table": False,
1857        "using": False,
1858        "where": False,
1859        "columns": False,
1860        "unique": False,
1861        "primary": False,
1862        "amp": False,  # teradata
1863        "include": False,
1864        "partition_by": False,  # teradata
1865    }
1866
1867
1868class Insert(DDL, DML):
1869    arg_types = {
1870        "hint": False,
1871        "with": False,
1872        "this": True,
1873        "expression": False,
1874        "conflict": False,
1875        "returning": False,
1876        "overwrite": False,
1877        "exists": False,
1878        "partition": False,
1879        "alternative": False,
1880        "where": False,
1881        "ignore": False,
1882        "by_name": False,
1883    }
1884
1885    def with_(
1886        self,
1887        alias: ExpOrStr,
1888        as_: ExpOrStr,
1889        recursive: t.Optional[bool] = None,
1890        append: bool = True,
1891        dialect: DialectType = None,
1892        copy: bool = True,
1893        **opts,
1894    ) -> Insert:
1895        """
1896        Append to or set the common table expressions.
1897
1898        Example:
1899            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1900            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1901
1902        Args:
1903            alias: the SQL code string to parse as the table name.
1904                If an `Expression` instance is passed, this is used as-is.
1905            as_: the SQL code string to parse as the table expression.
1906                If an `Expression` instance is passed, it will be used as-is.
1907            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1908            append: if `True`, add to any existing expressions.
1909                Otherwise, this resets the expressions.
1910            dialect: the dialect used to parse the input expression.
1911            copy: if `False`, modify this expression instance in-place.
1912            opts: other options to use to parse the input expressions.
1913
1914        Returns:
1915            The modified expression.
1916        """
1917        return _apply_cte_builder(
1918            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1919        )
1920
1921
1922class OnConflict(Expression):
1923    arg_types = {
1924        "duplicate": False,
1925        "expressions": False,
1926        "action": False,
1927        "conflict_keys": False,
1928        "constraint": False,
1929    }
1930
1931
1932class Returning(Expression):
1933    arg_types = {"expressions": True, "into": False}
1934
1935
1936# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1937class Introducer(Expression):
1938    arg_types = {"this": True, "expression": True}
1939
1940
1941# national char, like n'utf8'
1942class National(Expression):
1943    pass
1944
1945
1946class LoadData(Expression):
1947    arg_types = {
1948        "this": True,
1949        "local": False,
1950        "overwrite": False,
1951        "inpath": True,
1952        "partition": False,
1953        "input_format": False,
1954        "serde": False,
1955    }
1956
1957
1958class Partition(Expression):
1959    arg_types = {"expressions": True}
1960
1961
1962class PartitionRange(Expression):
1963    arg_types = {"this": True, "expression": True}
1964
1965
1966class Fetch(Expression):
1967    arg_types = {
1968        "direction": False,
1969        "count": False,
1970        "percent": False,
1971        "with_ties": False,
1972    }
1973
1974
1975class Group(Expression):
1976    arg_types = {
1977        "expressions": False,
1978        "grouping_sets": False,
1979        "cube": False,
1980        "rollup": False,
1981        "totals": False,
1982        "all": False,
1983    }
1984
1985
1986class Lambda(Expression):
1987    arg_types = {"this": True, "expressions": True}
1988
1989
1990class Limit(Expression):
1991    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
1992
1993
1994class Literal(Condition):
1995    arg_types = {"this": True, "is_string": True}
1996
1997    @property
1998    def hashable_args(self) -> t.Any:
1999        return (self.this, self.args.get("is_string"))
2000
2001    @classmethod
2002    def number(cls, number) -> Literal:
2003        return cls(this=str(number), is_string=False)
2004
2005    @classmethod
2006    def string(cls, string) -> Literal:
2007        return cls(this=str(string), is_string=True)
2008
2009    @property
2010    def output_name(self) -> str:
2011        return self.name
2012
2013
2014class Join(Expression):
2015    arg_types = {
2016        "this": True,
2017        "on": False,
2018        "side": False,
2019        "kind": False,
2020        "using": False,
2021        "method": False,
2022        "global": False,
2023        "hint": False,
2024    }
2025
2026    @property
2027    def method(self) -> str:
2028        return self.text("method").upper()
2029
2030    @property
2031    def kind(self) -> str:
2032        return self.text("kind").upper()
2033
2034    @property
2035    def side(self) -> str:
2036        return self.text("side").upper()
2037
2038    @property
2039    def hint(self) -> str:
2040        return self.text("hint").upper()
2041
2042    @property
2043    def alias_or_name(self) -> str:
2044        return self.this.alias_or_name
2045
2046    def on(
2047        self,
2048        *expressions: t.Optional[ExpOrStr],
2049        append: bool = True,
2050        dialect: DialectType = None,
2051        copy: bool = True,
2052        **opts,
2053    ) -> Join:
2054        """
2055        Append to or set the ON expressions.
2056
2057        Example:
2058            >>> import sqlglot
2059            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2060            'JOIN x ON y = 1'
2061
2062        Args:
2063            *expressions: the SQL code strings to parse.
2064                If an `Expression` instance is passed, it will be used as-is.
2065                Multiple expressions are combined with an AND operator.
2066            append: if `True`, AND the new expressions to any existing expression.
2067                Otherwise, this resets the expression.
2068            dialect: the dialect used to parse the input expressions.
2069            copy: if `False`, modify this expression instance in-place.
2070            opts: other options to use to parse the input expressions.
2071
2072        Returns:
2073            The modified Join expression.
2074        """
2075        join = _apply_conjunction_builder(
2076            *expressions,
2077            instance=self,
2078            arg="on",
2079            append=append,
2080            dialect=dialect,
2081            copy=copy,
2082            **opts,
2083        )
2084
2085        if join.kind == "CROSS":
2086            join.set("kind", None)
2087
2088        return join
2089
2090    def using(
2091        self,
2092        *expressions: t.Optional[ExpOrStr],
2093        append: bool = True,
2094        dialect: DialectType = None,
2095        copy: bool = True,
2096        **opts,
2097    ) -> Join:
2098        """
2099        Append to or set the USING expressions.
2100
2101        Example:
2102            >>> import sqlglot
2103            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2104            'JOIN x USING (foo, bla)'
2105
2106        Args:
2107            *expressions: the SQL code strings to parse.
2108                If an `Expression` instance is passed, it will be used as-is.
2109            append: if `True`, concatenate the new expressions to the existing "using" list.
2110                Otherwise, this resets the expression.
2111            dialect: the dialect used to parse the input expressions.
2112            copy: if `False`, modify this expression instance in-place.
2113            opts: other options to use to parse the input expressions.
2114
2115        Returns:
2116            The modified Join expression.
2117        """
2118        join = _apply_list_builder(
2119            *expressions,
2120            instance=self,
2121            arg="using",
2122            append=append,
2123            dialect=dialect,
2124            copy=copy,
2125            **opts,
2126        )
2127
2128        if join.kind == "CROSS":
2129            join.set("kind", None)
2130
2131        return join
2132
2133
2134class Lateral(UDTF):
2135    arg_types = {
2136        "this": True,
2137        "view": False,
2138        "outer": False,
2139        "alias": False,
2140        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2141    }
2142
2143
2144class MatchRecognize(Expression):
2145    arg_types = {
2146        "partition_by": False,
2147        "order": False,
2148        "measures": False,
2149        "rows": False,
2150        "after": False,
2151        "pattern": False,
2152        "define": False,
2153        "alias": False,
2154    }
2155
2156
2157# Clickhouse FROM FINAL modifier
2158# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2159class Final(Expression):
2160    pass
2161
2162
2163class Offset(Expression):
2164    arg_types = {"this": False, "expression": True, "expressions": False}
2165
2166
2167class Order(Expression):
2168    arg_types = {
2169        "this": False,
2170        "expressions": True,
2171        "interpolate": False,
2172        "siblings": False,
2173    }
2174
2175
2176# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2177class WithFill(Expression):
2178    arg_types = {"from": False, "to": False, "step": False}
2179
2180
2181# hive specific sorts
2182# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2183class Cluster(Order):
2184    pass
2185
2186
2187class Distribute(Order):
2188    pass
2189
2190
2191class Sort(Order):
2192    pass
2193
2194
2195class Ordered(Expression):
2196    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2197
2198
2199class Property(Expression):
2200    arg_types = {"this": True, "value": True}
2201
2202
2203class AlgorithmProperty(Property):
2204    arg_types = {"this": True}
2205
2206
2207class AutoIncrementProperty(Property):
2208    arg_types = {"this": True}
2209
2210
2211# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2212class AutoRefreshProperty(Property):
2213    arg_types = {"this": True}
2214
2215
2216class BlockCompressionProperty(Property):
2217    arg_types = {
2218        "autotemp": False,
2219        "always": False,
2220        "default": False,
2221        "manual": False,
2222        "never": False,
2223    }
2224
2225
2226class CharacterSetProperty(Property):
2227    arg_types = {"this": True, "default": True}
2228
2229
2230class ChecksumProperty(Property):
2231    arg_types = {"on": False, "default": False}
2232
2233
2234class CollateProperty(Property):
2235    arg_types = {"this": True, "default": False}
2236
2237
2238class CopyGrantsProperty(Property):
2239    arg_types = {}
2240
2241
2242class DataBlocksizeProperty(Property):
2243    arg_types = {
2244        "size": False,
2245        "units": False,
2246        "minimum": False,
2247        "maximum": False,
2248        "default": False,
2249    }
2250
2251
2252class DefinerProperty(Property):
2253    arg_types = {"this": True}
2254
2255
2256class DistKeyProperty(Property):
2257    arg_types = {"this": True}
2258
2259
2260class DistStyleProperty(Property):
2261    arg_types = {"this": True}
2262
2263
2264class EngineProperty(Property):
2265    arg_types = {"this": True}
2266
2267
2268class HeapProperty(Property):
2269    arg_types = {}
2270
2271
2272class ToTableProperty(Property):
2273    arg_types = {"this": True}
2274
2275
2276class ExecuteAsProperty(Property):
2277    arg_types = {"this": True}
2278
2279
2280class ExternalProperty(Property):
2281    arg_types = {"this": False}
2282
2283
2284class FallbackProperty(Property):
2285    arg_types = {"no": True, "protection": False}
2286
2287
2288class FileFormatProperty(Property):
2289    arg_types = {"this": True}
2290
2291
2292class FreespaceProperty(Property):
2293    arg_types = {"this": True, "percent": False}
2294
2295
2296class GlobalProperty(Property):
2297    arg_types = {}
2298
2299
2300class InheritsProperty(Property):
2301    arg_types = {"expressions": True}
2302
2303
2304class InputModelProperty(Property):
2305    arg_types = {"this": True}
2306
2307
2308class OutputModelProperty(Property):
2309    arg_types = {"this": True}
2310
2311
2312class IsolatedLoadingProperty(Property):
2313    arg_types = {
2314        "no": False,
2315        "concurrent": False,
2316        "for_all": False,
2317        "for_insert": False,
2318        "for_none": False,
2319    }
2320
2321
2322class JournalProperty(Property):
2323    arg_types = {
2324        "no": False,
2325        "dual": False,
2326        "before": False,
2327        "local": False,
2328        "after": False,
2329    }
2330
2331
2332class LanguageProperty(Property):
2333    arg_types = {"this": True}
2334
2335
2336# spark ddl
2337class ClusteredByProperty(Property):
2338    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2339
2340
2341class DictProperty(Property):
2342    arg_types = {"this": True, "kind": True, "settings": False}
2343
2344
2345class DictSubProperty(Property):
2346    pass
2347
2348
2349class DictRange(Property):
2350    arg_types = {"this": True, "min": True, "max": True}
2351
2352
2353# Clickhouse CREATE ... ON CLUSTER modifier
2354# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2355class OnCluster(Property):
2356    arg_types = {"this": True}
2357
2358
2359class LikeProperty(Property):
2360    arg_types = {"this": True, "expressions": False}
2361
2362
2363class LocationProperty(Property):
2364    arg_types = {"this": True}
2365
2366
2367class LockProperty(Property):
2368    arg_types = {"this": True}
2369
2370
2371class LockingProperty(Property):
2372    arg_types = {
2373        "this": False,
2374        "kind": True,
2375        "for_or_in": False,
2376        "lock_type": True,
2377        "override": False,
2378    }
2379
2380
2381class LogProperty(Property):
2382    arg_types = {"no": True}
2383
2384
2385class MaterializedProperty(Property):
2386    arg_types = {"this": False}
2387
2388
2389class MergeBlockRatioProperty(Property):
2390    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2391
2392
2393class NoPrimaryIndexProperty(Property):
2394    arg_types = {}
2395
2396
2397class OnProperty(Property):
2398    arg_types = {"this": True}
2399
2400
2401class OnCommitProperty(Property):
2402    arg_types = {"delete": False}
2403
2404
2405class PartitionedByProperty(Property):
2406    arg_types = {"this": True}
2407
2408
2409# https://www.postgresql.org/docs/current/sql-createtable.html
2410class PartitionBoundSpec(Expression):
2411    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2412    arg_types = {
2413        "this": False,
2414        "expression": False,
2415        "from_expressions": False,
2416        "to_expressions": False,
2417    }
2418
2419
2420class PartitionedOfProperty(Property):
2421    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2422    arg_types = {"this": True, "expression": True}
2423
2424
2425class RemoteWithConnectionModelProperty(Property):
2426    arg_types = {"this": True}
2427
2428
2429class ReturnsProperty(Property):
2430    arg_types = {"this": True, "is_table": False, "table": False}
2431
2432
2433class RowFormatProperty(Property):
2434    arg_types = {"this": True}
2435
2436
2437class RowFormatDelimitedProperty(Property):
2438    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2439    arg_types = {
2440        "fields": False,
2441        "escaped": False,
2442        "collection_items": False,
2443        "map_keys": False,
2444        "lines": False,
2445        "null": False,
2446        "serde": False,
2447    }
2448
2449
2450class RowFormatSerdeProperty(Property):
2451    arg_types = {"this": True, "serde_properties": False}
2452
2453
2454# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2455class QueryTransform(Expression):
2456    arg_types = {
2457        "expressions": True,
2458        "command_script": True,
2459        "schema": False,
2460        "row_format_before": False,
2461        "record_writer": False,
2462        "row_format_after": False,
2463        "record_reader": False,
2464    }
2465
2466
2467class SampleProperty(Property):
2468    arg_types = {"this": True}
2469
2470
2471class SchemaCommentProperty(Property):
2472    arg_types = {"this": True}
2473
2474
2475class SerdeProperties(Property):
2476    arg_types = {"expressions": True}
2477
2478
2479class SetProperty(Property):
2480    arg_types = {"multi": True}
2481
2482
2483class SharingProperty(Property):
2484    arg_types = {"this": False}
2485
2486
2487class SetConfigProperty(Property):
2488    arg_types = {"this": True}
2489
2490
2491class SettingsProperty(Property):
2492    arg_types = {"expressions": True}
2493
2494
2495class SortKeyProperty(Property):
2496    arg_types = {"this": True, "compound": False}
2497
2498
2499class SqlReadWriteProperty(Property):
2500    arg_types = {"this": True}
2501
2502
2503class SqlSecurityProperty(Property):
2504    arg_types = {"definer": True}
2505
2506
2507class StabilityProperty(Property):
2508    arg_types = {"this": True}
2509
2510
2511class TemporaryProperty(Property):
2512    arg_types = {"this": False}
2513
2514
2515class TransformModelProperty(Property):
2516    arg_types = {"expressions": True}
2517
2518
2519class TransientProperty(Property):
2520    arg_types = {"this": False}
2521
2522
2523class UnloggedProperty(Property):
2524    arg_types = {}
2525
2526
2527class VolatileProperty(Property):
2528    arg_types = {"this": False}
2529
2530
2531class WithDataProperty(Property):
2532    arg_types = {"no": True, "statistics": False}
2533
2534
2535class WithJournalTableProperty(Property):
2536    arg_types = {"this": True}
2537
2538
2539class WithSystemVersioningProperty(Property):
2540    # this -> history table name, expression -> data consistency check
2541    arg_types = {"this": False, "expression": False}
2542
2543
2544class Properties(Expression):
2545    arg_types = {"expressions": True}
2546
2547    NAME_TO_PROPERTY = {
2548        "ALGORITHM": AlgorithmProperty,
2549        "AUTO_INCREMENT": AutoIncrementProperty,
2550        "CHARACTER SET": CharacterSetProperty,
2551        "CLUSTERED_BY": ClusteredByProperty,
2552        "COLLATE": CollateProperty,
2553        "COMMENT": SchemaCommentProperty,
2554        "DEFINER": DefinerProperty,
2555        "DISTKEY": DistKeyProperty,
2556        "DISTSTYLE": DistStyleProperty,
2557        "ENGINE": EngineProperty,
2558        "EXECUTE AS": ExecuteAsProperty,
2559        "FORMAT": FileFormatProperty,
2560        "LANGUAGE": LanguageProperty,
2561        "LOCATION": LocationProperty,
2562        "LOCK": LockProperty,
2563        "PARTITIONED_BY": PartitionedByProperty,
2564        "RETURNS": ReturnsProperty,
2565        "ROW_FORMAT": RowFormatProperty,
2566        "SORTKEY": SortKeyProperty,
2567    }
2568
2569    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2570
2571    # CREATE property locations
2572    # Form: schema specified
2573    #   create [POST_CREATE]
2574    #     table a [POST_NAME]
2575    #     (b int) [POST_SCHEMA]
2576    #     with ([POST_WITH])
2577    #     index (b) [POST_INDEX]
2578    #
2579    # Form: alias selection
2580    #   create [POST_CREATE]
2581    #     table a [POST_NAME]
2582    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2583    #     index (c) [POST_INDEX]
2584    class Location(AutoName):
2585        POST_CREATE = auto()
2586        POST_NAME = auto()
2587        POST_SCHEMA = auto()
2588        POST_WITH = auto()
2589        POST_ALIAS = auto()
2590        POST_EXPRESSION = auto()
2591        POST_INDEX = auto()
2592        UNSUPPORTED = auto()
2593
2594    @classmethod
2595    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2596        expressions = []
2597        for key, value in properties_dict.items():
2598            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2599            if property_cls:
2600                expressions.append(property_cls(this=convert(value)))
2601            else:
2602                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2603
2604        return cls(expressions=expressions)
2605
2606
2607class Qualify(Expression):
2608    pass
2609
2610
2611class InputOutputFormat(Expression):
2612    arg_types = {"input_format": False, "output_format": False}
2613
2614
2615# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2616class Return(Expression):
2617    pass
2618
2619
2620class Reference(Expression):
2621    arg_types = {"this": True, "expressions": False, "options": False}
2622
2623
2624class Tuple(Expression):
2625    arg_types = {"expressions": False}
2626
2627    def isin(
2628        self,
2629        *expressions: t.Any,
2630        query: t.Optional[ExpOrStr] = None,
2631        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2632        copy: bool = True,
2633        **opts,
2634    ) -> In:
2635        return In(
2636            this=maybe_copy(self, copy),
2637            expressions=[convert(e, copy=copy) for e in expressions],
2638            query=maybe_parse(query, copy=copy, **opts) if query else None,
2639            unnest=(
2640                Unnest(
2641                    expressions=[
2642                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2643                        for e in ensure_list(unnest)
2644                    ]
2645                )
2646                if unnest
2647                else None
2648            ),
2649        )
2650
2651
2652QUERY_MODIFIERS = {
2653    "match": False,
2654    "laterals": False,
2655    "joins": False,
2656    "connect": False,
2657    "pivots": False,
2658    "prewhere": False,
2659    "where": False,
2660    "group": False,
2661    "having": False,
2662    "qualify": False,
2663    "windows": False,
2664    "distribute": False,
2665    "sort": False,
2666    "cluster": False,
2667    "order": False,
2668    "limit": False,
2669    "offset": False,
2670    "locks": False,
2671    "sample": False,
2672    "settings": False,
2673    "format": False,
2674    "options": False,
2675}
2676
2677
2678# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2679# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2680class QueryOption(Expression):
2681    arg_types = {"this": True, "expression": False}
2682
2683
2684# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2685class WithTableHint(Expression):
2686    arg_types = {"expressions": True}
2687
2688
2689# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2690class IndexTableHint(Expression):
2691    arg_types = {"this": True, "expressions": False, "target": False}
2692
2693
2694# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2695class HistoricalData(Expression):
2696    arg_types = {"this": True, "kind": True, "expression": True}
2697
2698
2699class Table(Expression):
2700    arg_types = {
2701        "this": False,
2702        "alias": False,
2703        "db": False,
2704        "catalog": False,
2705        "laterals": False,
2706        "joins": False,
2707        "pivots": False,
2708        "hints": False,
2709        "system_time": False,
2710        "version": False,
2711        "format": False,
2712        "pattern": False,
2713        "ordinality": False,
2714        "when": False,
2715        "only": False,
2716    }
2717
2718    @property
2719    def name(self) -> str:
2720        if isinstance(self.this, Func):
2721            return ""
2722        return self.this.name
2723
2724    @property
2725    def db(self) -> str:
2726        return self.text("db")
2727
2728    @property
2729    def catalog(self) -> str:
2730        return self.text("catalog")
2731
2732    @property
2733    def selects(self) -> t.List[Expression]:
2734        return []
2735
2736    @property
2737    def named_selects(self) -> t.List[str]:
2738        return []
2739
2740    @property
2741    def parts(self) -> t.List[Expression]:
2742        """Return the parts of a table in order catalog, db, table."""
2743        parts: t.List[Expression] = []
2744
2745        for arg in ("catalog", "db", "this"):
2746            part = self.args.get(arg)
2747
2748            if isinstance(part, Dot):
2749                parts.extend(part.flatten())
2750            elif isinstance(part, Expression):
2751                parts.append(part)
2752
2753        return parts
2754
2755    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2756        parts = self.parts
2757        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2758        alias = self.args.get("alias")
2759        if alias:
2760            col = alias_(col, alias.this, copy=copy)
2761        return col
2762
2763
2764class Union(Query):
2765    arg_types = {
2766        "with": False,
2767        "this": True,
2768        "expression": True,
2769        "distinct": False,
2770        "by_name": False,
2771        **QUERY_MODIFIERS,
2772    }
2773
2774    def select(
2775        self,
2776        *expressions: t.Optional[ExpOrStr],
2777        append: bool = True,
2778        dialect: DialectType = None,
2779        copy: bool = True,
2780        **opts,
2781    ) -> Union:
2782        this = maybe_copy(self, copy)
2783        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2784        this.expression.unnest().select(
2785            *expressions, append=append, dialect=dialect, copy=False, **opts
2786        )
2787        return this
2788
2789    @property
2790    def named_selects(self) -> t.List[str]:
2791        return self.this.unnest().named_selects
2792
2793    @property
2794    def is_star(self) -> bool:
2795        return self.this.is_star or self.expression.is_star
2796
2797    @property
2798    def selects(self) -> t.List[Expression]:
2799        return self.this.unnest().selects
2800
2801    @property
2802    def left(self) -> Expression:
2803        return self.this
2804
2805    @property
2806    def right(self) -> Expression:
2807        return self.expression
2808
2809
2810class Except(Union):
2811    pass
2812
2813
2814class Intersect(Union):
2815    pass
2816
2817
2818class Unnest(UDTF):
2819    arg_types = {
2820        "expressions": True,
2821        "alias": False,
2822        "offset": False,
2823    }
2824
2825    @property
2826    def selects(self) -> t.List[Expression]:
2827        columns = super().selects
2828        offset = self.args.get("offset")
2829        if offset:
2830            columns = columns + [to_identifier("offset") if offset is True else offset]
2831        return columns
2832
2833
2834class Update(Expression):
2835    arg_types = {
2836        "with": False,
2837        "this": False,
2838        "expressions": True,
2839        "from": False,
2840        "where": False,
2841        "returning": False,
2842        "order": False,
2843        "limit": False,
2844    }
2845
2846
2847class Values(UDTF):
2848    arg_types = {"expressions": True, "alias": False}
2849
2850
2851class Var(Expression):
2852    pass
2853
2854
2855class Version(Expression):
2856    """
2857    Time travel, iceberg, bigquery etc
2858    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2859    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2860    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2861    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2862    this is either TIMESTAMP or VERSION
2863    kind is ("AS OF", "BETWEEN")
2864    """
2865
2866    arg_types = {"this": True, "kind": True, "expression": False}
2867
2868
2869class Schema(Expression):
2870    arg_types = {"this": False, "expressions": False}
2871
2872
2873# https://dev.mysql.com/doc/refman/8.0/en/select.html
2874# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2875class Lock(Expression):
2876    arg_types = {"update": True, "expressions": False, "wait": False}
2877
2878
2879class Select(Query):
2880    arg_types = {
2881        "with": False,
2882        "kind": False,
2883        "expressions": False,
2884        "hint": False,
2885        "distinct": False,
2886        "into": False,
2887        "from": False,
2888        **QUERY_MODIFIERS,
2889    }
2890
2891    def from_(
2892        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2893    ) -> Select:
2894        """
2895        Set the FROM expression.
2896
2897        Example:
2898            >>> Select().from_("tbl").select("x").sql()
2899            'SELECT x FROM tbl'
2900
2901        Args:
2902            expression : the SQL code strings to parse.
2903                If a `From` instance is passed, this is used as-is.
2904                If another `Expression` instance is passed, it will be wrapped in a `From`.
2905            dialect: the dialect used to parse the input expression.
2906            copy: if `False`, modify this expression instance in-place.
2907            opts: other options to use to parse the input expressions.
2908
2909        Returns:
2910            The modified Select expression.
2911        """
2912        return _apply_builder(
2913            expression=expression,
2914            instance=self,
2915            arg="from",
2916            into=From,
2917            prefix="FROM",
2918            dialect=dialect,
2919            copy=copy,
2920            **opts,
2921        )
2922
2923    def group_by(
2924        self,
2925        *expressions: t.Optional[ExpOrStr],
2926        append: bool = True,
2927        dialect: DialectType = None,
2928        copy: bool = True,
2929        **opts,
2930    ) -> Select:
2931        """
2932        Set the GROUP BY expression.
2933
2934        Example:
2935            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2936            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2937
2938        Args:
2939            *expressions: the SQL code strings to parse.
2940                If a `Group` instance is passed, this is used as-is.
2941                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2942                If nothing is passed in then a group by is not applied to the expression
2943            append: if `True`, add to any existing expressions.
2944                Otherwise, this flattens all the `Group` expression into a single expression.
2945            dialect: the dialect used to parse the input expression.
2946            copy: if `False`, modify this expression instance in-place.
2947            opts: other options to use to parse the input expressions.
2948
2949        Returns:
2950            The modified Select expression.
2951        """
2952        if not expressions:
2953            return self if not copy else self.copy()
2954
2955        return _apply_child_list_builder(
2956            *expressions,
2957            instance=self,
2958            arg="group",
2959            append=append,
2960            copy=copy,
2961            prefix="GROUP BY",
2962            into=Group,
2963            dialect=dialect,
2964            **opts,
2965        )
2966
2967    def order_by(
2968        self,
2969        *expressions: t.Optional[ExpOrStr],
2970        append: bool = True,
2971        dialect: DialectType = None,
2972        copy: bool = True,
2973        **opts,
2974    ) -> Select:
2975        """
2976        Set the ORDER BY expression.
2977
2978        Example:
2979            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2980            'SELECT x FROM tbl ORDER BY x DESC'
2981
2982        Args:
2983            *expressions: the SQL code strings to parse.
2984                If a `Group` instance is passed, this is used as-is.
2985                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2986            append: if `True`, add to any existing expressions.
2987                Otherwise, this flattens all the `Order` expression into a single expression.
2988            dialect: the dialect used to parse the input expression.
2989            copy: if `False`, modify this expression instance in-place.
2990            opts: other options to use to parse the input expressions.
2991
2992        Returns:
2993            The modified Select expression.
2994        """
2995        return _apply_child_list_builder(
2996            *expressions,
2997            instance=self,
2998            arg="order",
2999            append=append,
3000            copy=copy,
3001            prefix="ORDER BY",
3002            into=Order,
3003            dialect=dialect,
3004            **opts,
3005        )
3006
3007    def sort_by(
3008        self,
3009        *expressions: t.Optional[ExpOrStr],
3010        append: bool = True,
3011        dialect: DialectType = None,
3012        copy: bool = True,
3013        **opts,
3014    ) -> Select:
3015        """
3016        Set the SORT BY expression.
3017
3018        Example:
3019            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3020            'SELECT x FROM tbl SORT BY x DESC'
3021
3022        Args:
3023            *expressions: the SQL code strings to parse.
3024                If a `Group` instance is passed, this is used as-is.
3025                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3026            append: if `True`, add to any existing expressions.
3027                Otherwise, this flattens all the `Order` expression into a single expression.
3028            dialect: the dialect used to parse the input expression.
3029            copy: if `False`, modify this expression instance in-place.
3030            opts: other options to use to parse the input expressions.
3031
3032        Returns:
3033            The modified Select expression.
3034        """
3035        return _apply_child_list_builder(
3036            *expressions,
3037            instance=self,
3038            arg="sort",
3039            append=append,
3040            copy=copy,
3041            prefix="SORT BY",
3042            into=Sort,
3043            dialect=dialect,
3044            **opts,
3045        )
3046
3047    def cluster_by(
3048        self,
3049        *expressions: t.Optional[ExpOrStr],
3050        append: bool = True,
3051        dialect: DialectType = None,
3052        copy: bool = True,
3053        **opts,
3054    ) -> Select:
3055        """
3056        Set the CLUSTER BY expression.
3057
3058        Example:
3059            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3060            'SELECT x FROM tbl CLUSTER BY x DESC'
3061
3062        Args:
3063            *expressions: the SQL code strings to parse.
3064                If a `Group` instance is passed, this is used as-is.
3065                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3066            append: if `True`, add to any existing expressions.
3067                Otherwise, this flattens all the `Order` expression into a single expression.
3068            dialect: the dialect used to parse the input expression.
3069            copy: if `False`, modify this expression instance in-place.
3070            opts: other options to use to parse the input expressions.
3071
3072        Returns:
3073            The modified Select expression.
3074        """
3075        return _apply_child_list_builder(
3076            *expressions,
3077            instance=self,
3078            arg="cluster",
3079            append=append,
3080            copy=copy,
3081            prefix="CLUSTER BY",
3082            into=Cluster,
3083            dialect=dialect,
3084            **opts,
3085        )
3086
3087    def limit(
3088        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3089    ) -> Select:
3090        return _apply_builder(
3091            expression=expression,
3092            instance=self,
3093            arg="limit",
3094            into=Limit,
3095            prefix="LIMIT",
3096            dialect=dialect,
3097            copy=copy,
3098            into_arg="expression",
3099            **opts,
3100        )
3101
3102    def offset(
3103        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3104    ) -> Select:
3105        """
3106        Set the OFFSET expression.
3107
3108        Example:
3109            >>> Select().from_("tbl").select("x").offset(10).sql()
3110            'SELECT x FROM tbl OFFSET 10'
3111
3112        Args:
3113            expression: the SQL code string to parse.
3114                This can also be an integer.
3115                If a `Offset` instance is passed, this is used as-is.
3116                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3117            dialect: the dialect used to parse the input expression.
3118            copy: if `False`, modify this expression instance in-place.
3119            opts: other options to use to parse the input expressions.
3120
3121        Returns:
3122            The modified Select expression.
3123        """
3124        return _apply_builder(
3125            expression=expression,
3126            instance=self,
3127            arg="offset",
3128            into=Offset,
3129            prefix="OFFSET",
3130            dialect=dialect,
3131            copy=copy,
3132            into_arg="expression",
3133            **opts,
3134        )
3135
3136    def select(
3137        self,
3138        *expressions: t.Optional[ExpOrStr],
3139        append: bool = True,
3140        dialect: DialectType = None,
3141        copy: bool = True,
3142        **opts,
3143    ) -> Select:
3144        return _apply_list_builder(
3145            *expressions,
3146            instance=self,
3147            arg="expressions",
3148            append=append,
3149            dialect=dialect,
3150            into=Expression,
3151            copy=copy,
3152            **opts,
3153        )
3154
3155    def lateral(
3156        self,
3157        *expressions: t.Optional[ExpOrStr],
3158        append: bool = True,
3159        dialect: DialectType = None,
3160        copy: bool = True,
3161        **opts,
3162    ) -> Select:
3163        """
3164        Append to or set the LATERAL expressions.
3165
3166        Example:
3167            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3168            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3169
3170        Args:
3171            *expressions: the SQL code strings to parse.
3172                If an `Expression` instance is passed, it will be used as-is.
3173            append: if `True`, add to any existing expressions.
3174                Otherwise, this resets the expressions.
3175            dialect: the dialect used to parse the input expressions.
3176            copy: if `False`, modify this expression instance in-place.
3177            opts: other options to use to parse the input expressions.
3178
3179        Returns:
3180            The modified Select expression.
3181        """
3182        return _apply_list_builder(
3183            *expressions,
3184            instance=self,
3185            arg="laterals",
3186            append=append,
3187            into=Lateral,
3188            prefix="LATERAL VIEW",
3189            dialect=dialect,
3190            copy=copy,
3191            **opts,
3192        )
3193
3194    def join(
3195        self,
3196        expression: ExpOrStr,
3197        on: t.Optional[ExpOrStr] = None,
3198        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3199        append: bool = True,
3200        join_type: t.Optional[str] = None,
3201        join_alias: t.Optional[Identifier | str] = None,
3202        dialect: DialectType = None,
3203        copy: bool = True,
3204        **opts,
3205    ) -> Select:
3206        """
3207        Append to or set the JOIN expressions.
3208
3209        Example:
3210            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3211            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3212
3213            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3214            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3215
3216            Use `join_type` to change the type of join:
3217
3218            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3219            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3220
3221        Args:
3222            expression: the SQL code string to parse.
3223                If an `Expression` instance is passed, it will be used as-is.
3224            on: optionally specify the join "on" criteria as a SQL string.
3225                If an `Expression` instance is passed, it will be used as-is.
3226            using: optionally specify the join "using" criteria as a SQL string.
3227                If an `Expression` instance is passed, it will be used as-is.
3228            append: if `True`, add to any existing expressions.
3229                Otherwise, this resets the expressions.
3230            join_type: if set, alter the parsed join type.
3231            join_alias: an optional alias for the joined source.
3232            dialect: the dialect used to parse the input expressions.
3233            copy: if `False`, modify this expression instance in-place.
3234            opts: other options to use to parse the input expressions.
3235
3236        Returns:
3237            Select: the modified expression.
3238        """
3239        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3240
3241        try:
3242            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3243        except ParseError:
3244            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3245
3246        join = expression if isinstance(expression, Join) else Join(this=expression)
3247
3248        if isinstance(join.this, Select):
3249            join.this.replace(join.this.subquery())
3250
3251        if join_type:
3252            method: t.Optional[Token]
3253            side: t.Optional[Token]
3254            kind: t.Optional[Token]
3255
3256            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3257
3258            if method:
3259                join.set("method", method.text)
3260            if side:
3261                join.set("side", side.text)
3262            if kind:
3263                join.set("kind", kind.text)
3264
3265        if on:
3266            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3267            join.set("on", on)
3268
3269        if using:
3270            join = _apply_list_builder(
3271                *ensure_list(using),
3272                instance=join,
3273                arg="using",
3274                append=append,
3275                copy=copy,
3276                into=Identifier,
3277                **opts,
3278            )
3279
3280        if join_alias:
3281            join.set("this", alias_(join.this, join_alias, table=True))
3282
3283        return _apply_list_builder(
3284            join,
3285            instance=self,
3286            arg="joins",
3287            append=append,
3288            copy=copy,
3289            **opts,
3290        )
3291
3292    def where(
3293        self,
3294        *expressions: t.Optional[ExpOrStr],
3295        append: bool = True,
3296        dialect: DialectType = None,
3297        copy: bool = True,
3298        **opts,
3299    ) -> Select:
3300        """
3301        Append to or set the WHERE expressions.
3302
3303        Example:
3304            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3305            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3306
3307        Args:
3308            *expressions: the SQL code strings to parse.
3309                If an `Expression` instance is passed, it will be used as-is.
3310                Multiple expressions are combined with an AND operator.
3311            append: if `True`, AND the new expressions to any existing expression.
3312                Otherwise, this resets the expression.
3313            dialect: the dialect used to parse the input expressions.
3314            copy: if `False`, modify this expression instance in-place.
3315            opts: other options to use to parse the input expressions.
3316
3317        Returns:
3318            Select: the modified expression.
3319        """
3320        return _apply_conjunction_builder(
3321            *expressions,
3322            instance=self,
3323            arg="where",
3324            append=append,
3325            into=Where,
3326            dialect=dialect,
3327            copy=copy,
3328            **opts,
3329        )
3330
3331    def having(
3332        self,
3333        *expressions: t.Optional[ExpOrStr],
3334        append: bool = True,
3335        dialect: DialectType = None,
3336        copy: bool = True,
3337        **opts,
3338    ) -> Select:
3339        """
3340        Append to or set the HAVING expressions.
3341
3342        Example:
3343            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3344            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3345
3346        Args:
3347            *expressions: the SQL code strings to parse.
3348                If an `Expression` instance is passed, it will be used as-is.
3349                Multiple expressions are combined with an AND operator.
3350            append: if `True`, AND the new expressions to any existing expression.
3351                Otherwise, this resets the expression.
3352            dialect: the dialect used to parse the input expressions.
3353            copy: if `False`, modify this expression instance in-place.
3354            opts: other options to use to parse the input expressions.
3355
3356        Returns:
3357            The modified Select expression.
3358        """
3359        return _apply_conjunction_builder(
3360            *expressions,
3361            instance=self,
3362            arg="having",
3363            append=append,
3364            into=Having,
3365            dialect=dialect,
3366            copy=copy,
3367            **opts,
3368        )
3369
3370    def window(
3371        self,
3372        *expressions: t.Optional[ExpOrStr],
3373        append: bool = True,
3374        dialect: DialectType = None,
3375        copy: bool = True,
3376        **opts,
3377    ) -> Select:
3378        return _apply_list_builder(
3379            *expressions,
3380            instance=self,
3381            arg="windows",
3382            append=append,
3383            into=Window,
3384            dialect=dialect,
3385            copy=copy,
3386            **opts,
3387        )
3388
3389    def qualify(
3390        self,
3391        *expressions: t.Optional[ExpOrStr],
3392        append: bool = True,
3393        dialect: DialectType = None,
3394        copy: bool = True,
3395        **opts,
3396    ) -> Select:
3397        return _apply_conjunction_builder(
3398            *expressions,
3399            instance=self,
3400            arg="qualify",
3401            append=append,
3402            into=Qualify,
3403            dialect=dialect,
3404            copy=copy,
3405            **opts,
3406        )
3407
3408    def distinct(
3409        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3410    ) -> Select:
3411        """
3412        Set the OFFSET expression.
3413
3414        Example:
3415            >>> Select().from_("tbl").select("x").distinct().sql()
3416            'SELECT DISTINCT x FROM tbl'
3417
3418        Args:
3419            ons: the expressions to distinct on
3420            distinct: whether the Select should be distinct
3421            copy: if `False`, modify this expression instance in-place.
3422
3423        Returns:
3424            Select: the modified expression.
3425        """
3426        instance = maybe_copy(self, copy)
3427        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3428        instance.set("distinct", Distinct(on=on) if distinct else None)
3429        return instance
3430
3431    def ctas(
3432        self,
3433        table: ExpOrStr,
3434        properties: t.Optional[t.Dict] = None,
3435        dialect: DialectType = None,
3436        copy: bool = True,
3437        **opts,
3438    ) -> Create:
3439        """
3440        Convert this expression to a CREATE TABLE AS statement.
3441
3442        Example:
3443            >>> Select().select("*").from_("tbl").ctas("x").sql()
3444            'CREATE TABLE x AS SELECT * FROM tbl'
3445
3446        Args:
3447            table: the SQL code string to parse as the table name.
3448                If another `Expression` instance is passed, it will be used as-is.
3449            properties: an optional mapping of table properties
3450            dialect: the dialect used to parse the input table.
3451            copy: if `False`, modify this expression instance in-place.
3452            opts: other options to use to parse the input table.
3453
3454        Returns:
3455            The new Create expression.
3456        """
3457        instance = maybe_copy(self, copy)
3458        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3459
3460        properties_expression = None
3461        if properties:
3462            properties_expression = Properties.from_dict(properties)
3463
3464        return Create(
3465            this=table_expression,
3466            kind="TABLE",
3467            expression=instance,
3468            properties=properties_expression,
3469        )
3470
3471    def lock(self, update: bool = True, copy: bool = True) -> Select:
3472        """
3473        Set the locking read mode for this expression.
3474
3475        Examples:
3476            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3477            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3478
3479            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3480            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3481
3482        Args:
3483            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3484            copy: if `False`, modify this expression instance in-place.
3485
3486        Returns:
3487            The modified expression.
3488        """
3489        inst = maybe_copy(self, copy)
3490        inst.set("locks", [Lock(update=update)])
3491
3492        return inst
3493
3494    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3495        """
3496        Set hints for this expression.
3497
3498        Examples:
3499            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3500            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3501
3502        Args:
3503            hints: The SQL code strings to parse as the hints.
3504                If an `Expression` instance is passed, it will be used as-is.
3505            dialect: The dialect used to parse the hints.
3506            copy: If `False`, modify this expression instance in-place.
3507
3508        Returns:
3509            The modified expression.
3510        """
3511        inst = maybe_copy(self, copy)
3512        inst.set(
3513            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3514        )
3515
3516        return inst
3517
3518    @property
3519    def named_selects(self) -> t.List[str]:
3520        return [e.output_name for e in self.expressions if e.alias_or_name]
3521
3522    @property
3523    def is_star(self) -> bool:
3524        return any(expression.is_star for expression in self.expressions)
3525
3526    @property
3527    def selects(self) -> t.List[Expression]:
3528        return self.expressions
3529
3530
3531UNWRAPPED_QUERIES = (Select, Union)
3532
3533
3534class Subquery(DerivedTable, Query):
3535    arg_types = {
3536        "this": True,
3537        "alias": False,
3538        "with": False,
3539        **QUERY_MODIFIERS,
3540    }
3541
3542    def unnest(self):
3543        """Returns the first non subquery."""
3544        expression = self
3545        while isinstance(expression, Subquery):
3546            expression = expression.this
3547        return expression
3548
3549    def unwrap(self) -> Subquery:
3550        expression = self
3551        while expression.same_parent and expression.is_wrapper:
3552            expression = t.cast(Subquery, expression.parent)
3553        return expression
3554
3555    def select(
3556        self,
3557        *expressions: t.Optional[ExpOrStr],
3558        append: bool = True,
3559        dialect: DialectType = None,
3560        copy: bool = True,
3561        **opts,
3562    ) -> Subquery:
3563        this = maybe_copy(self, copy)
3564        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3565        return this
3566
3567    @property
3568    def is_wrapper(self) -> bool:
3569        """
3570        Whether this Subquery acts as a simple wrapper around another expression.
3571
3572        SELECT * FROM (((SELECT * FROM t)))
3573                      ^
3574                      This corresponds to a "wrapper" Subquery node
3575        """
3576        return all(v is None for k, v in self.args.items() if k != "this")
3577
3578    @property
3579    def is_star(self) -> bool:
3580        return self.this.is_star
3581
3582    @property
3583    def output_name(self) -> str:
3584        return self.alias
3585
3586
3587class TableSample(Expression):
3588    arg_types = {
3589        "this": False,
3590        "expressions": False,
3591        "method": False,
3592        "bucket_numerator": False,
3593        "bucket_denominator": False,
3594        "bucket_field": False,
3595        "percent": False,
3596        "rows": False,
3597        "size": False,
3598        "seed": False,
3599    }
3600
3601
3602class Tag(Expression):
3603    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3604
3605    arg_types = {
3606        "this": False,
3607        "prefix": False,
3608        "postfix": False,
3609    }
3610
3611
3612# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3613# https://duckdb.org/docs/sql/statements/pivot
3614class Pivot(Expression):
3615    arg_types = {
3616        "this": False,
3617        "alias": False,
3618        "expressions": False,
3619        "field": False,
3620        "unpivot": False,
3621        "using": False,
3622        "group": False,
3623        "columns": False,
3624        "include_nulls": False,
3625    }
3626
3627    @property
3628    def unpivot(self) -> bool:
3629        return bool(self.args.get("unpivot"))
3630
3631
3632class Window(Condition):
3633    arg_types = {
3634        "this": True,
3635        "partition_by": False,
3636        "order": False,
3637        "spec": False,
3638        "alias": False,
3639        "over": False,
3640        "first": False,
3641    }
3642
3643
3644class WindowSpec(Expression):
3645    arg_types = {
3646        "kind": False,
3647        "start": False,
3648        "start_side": False,
3649        "end": False,
3650        "end_side": False,
3651    }
3652
3653
3654class PreWhere(Expression):
3655    pass
3656
3657
3658class Where(Expression):
3659    pass
3660
3661
3662class Star(Expression):
3663    arg_types = {"except": False, "replace": False}
3664
3665    @property
3666    def name(self) -> str:
3667        return "*"
3668
3669    @property
3670    def output_name(self) -> str:
3671        return self.name
3672
3673
3674class Parameter(Condition):
3675    arg_types = {"this": True, "expression": False}
3676
3677
3678class SessionParameter(Condition):
3679    arg_types = {"this": True, "kind": False}
3680
3681
3682class Placeholder(Condition):
3683    arg_types = {"this": False, "kind": False}
3684
3685
3686class Null(Condition):
3687    arg_types: t.Dict[str, t.Any] = {}
3688
3689    @property
3690    def name(self) -> str:
3691        return "NULL"
3692
3693
3694class Boolean(Condition):
3695    pass
3696
3697
3698class DataTypeParam(Expression):
3699    arg_types = {"this": True, "expression": False}
3700
3701    @property
3702    def name(self) -> str:
3703        return self.this.name
3704
3705
3706class DataType(Expression):
3707    arg_types = {
3708        "this": True,
3709        "expressions": False,
3710        "nested": False,
3711        "values": False,
3712        "prefix": False,
3713        "kind": False,
3714    }
3715
3716    class Type(AutoName):
3717        ARRAY = auto()
3718        AGGREGATEFUNCTION = auto()
3719        SIMPLEAGGREGATEFUNCTION = auto()
3720        BIGDECIMAL = auto()
3721        BIGINT = auto()
3722        BIGSERIAL = auto()
3723        BINARY = auto()
3724        BIT = auto()
3725        BOOLEAN = auto()
3726        BPCHAR = auto()
3727        CHAR = auto()
3728        DATE = auto()
3729        DATE32 = auto()
3730        DATEMULTIRANGE = auto()
3731        DATERANGE = auto()
3732        DATETIME = auto()
3733        DATETIME64 = auto()
3734        DECIMAL = auto()
3735        DOUBLE = auto()
3736        ENUM = auto()
3737        ENUM8 = auto()
3738        ENUM16 = auto()
3739        FIXEDSTRING = auto()
3740        FLOAT = auto()
3741        GEOGRAPHY = auto()
3742        GEOMETRY = auto()
3743        HLLSKETCH = auto()
3744        HSTORE = auto()
3745        IMAGE = auto()
3746        INET = auto()
3747        INT = auto()
3748        INT128 = auto()
3749        INT256 = auto()
3750        INT4MULTIRANGE = auto()
3751        INT4RANGE = auto()
3752        INT8MULTIRANGE = auto()
3753        INT8RANGE = auto()
3754        INTERVAL = auto()
3755        IPADDRESS = auto()
3756        IPPREFIX = auto()
3757        IPV4 = auto()
3758        IPV6 = auto()
3759        JSON = auto()
3760        JSONB = auto()
3761        LONGBLOB = auto()
3762        LONGTEXT = auto()
3763        LOWCARDINALITY = auto()
3764        MAP = auto()
3765        MEDIUMBLOB = auto()
3766        MEDIUMINT = auto()
3767        MEDIUMTEXT = auto()
3768        MONEY = auto()
3769        NCHAR = auto()
3770        NESTED = auto()
3771        NULL = auto()
3772        NULLABLE = auto()
3773        NUMMULTIRANGE = auto()
3774        NUMRANGE = auto()
3775        NVARCHAR = auto()
3776        OBJECT = auto()
3777        ROWVERSION = auto()
3778        SERIAL = auto()
3779        SET = auto()
3780        SMALLINT = auto()
3781        SMALLMONEY = auto()
3782        SMALLSERIAL = auto()
3783        STRUCT = auto()
3784        SUPER = auto()
3785        TEXT = auto()
3786        TINYBLOB = auto()
3787        TINYTEXT = auto()
3788        TIME = auto()
3789        TIMETZ = auto()
3790        TIMESTAMP = auto()
3791        TIMESTAMPLTZ = auto()
3792        TIMESTAMPTZ = auto()
3793        TIMESTAMP_S = auto()
3794        TIMESTAMP_MS = auto()
3795        TIMESTAMP_NS = auto()
3796        TINYINT = auto()
3797        TSMULTIRANGE = auto()
3798        TSRANGE = auto()
3799        TSTZMULTIRANGE = auto()
3800        TSTZRANGE = auto()
3801        UBIGINT = auto()
3802        UINT = auto()
3803        UINT128 = auto()
3804        UINT256 = auto()
3805        UMEDIUMINT = auto()
3806        UDECIMAL = auto()
3807        UNIQUEIDENTIFIER = auto()
3808        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3809        USERDEFINED = "USER-DEFINED"
3810        USMALLINT = auto()
3811        UTINYINT = auto()
3812        UUID = auto()
3813        VARBINARY = auto()
3814        VARCHAR = auto()
3815        VARIANT = auto()
3816        XML = auto()
3817        YEAR = auto()
3818
3819    TEXT_TYPES = {
3820        Type.CHAR,
3821        Type.NCHAR,
3822        Type.NVARCHAR,
3823        Type.TEXT,
3824        Type.VARCHAR,
3825    }
3826
3827    INTEGER_TYPES = {
3828        Type.BIGINT,
3829        Type.BIT,
3830        Type.INT,
3831        Type.INT128,
3832        Type.INT256,
3833        Type.MEDIUMINT,
3834        Type.SMALLINT,
3835        Type.TINYINT,
3836        Type.UBIGINT,
3837        Type.UINT,
3838        Type.UINT128,
3839        Type.UINT256,
3840        Type.UMEDIUMINT,
3841        Type.USMALLINT,
3842        Type.UTINYINT,
3843    }
3844
3845    FLOAT_TYPES = {
3846        Type.DOUBLE,
3847        Type.FLOAT,
3848    }
3849
3850    REAL_TYPES = {
3851        *FLOAT_TYPES,
3852        Type.BIGDECIMAL,
3853        Type.DECIMAL,
3854        Type.MONEY,
3855        Type.SMALLMONEY,
3856        Type.UDECIMAL,
3857    }
3858
3859    NUMERIC_TYPES = {
3860        *INTEGER_TYPES,
3861        *REAL_TYPES,
3862    }
3863
3864    TEMPORAL_TYPES = {
3865        Type.DATE,
3866        Type.DATE32,
3867        Type.DATETIME,
3868        Type.DATETIME64,
3869        Type.TIME,
3870        Type.TIMESTAMP,
3871        Type.TIMESTAMPLTZ,
3872        Type.TIMESTAMPTZ,
3873        Type.TIMESTAMP_MS,
3874        Type.TIMESTAMP_NS,
3875        Type.TIMESTAMP_S,
3876        Type.TIMETZ,
3877    }
3878
3879    @classmethod
3880    def build(
3881        cls,
3882        dtype: DATA_TYPE,
3883        dialect: DialectType = None,
3884        udt: bool = False,
3885        copy: bool = True,
3886        **kwargs,
3887    ) -> DataType:
3888        """
3889        Constructs a DataType object.
3890
3891        Args:
3892            dtype: the data type of interest.
3893            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3894            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3895                DataType, thus creating a user-defined type.
3896            copy: whether to copy the data type.
3897            kwargs: additional arguments to pass in the constructor of DataType.
3898
3899        Returns:
3900            The constructed DataType object.
3901        """
3902        from sqlglot import parse_one
3903
3904        if isinstance(dtype, str):
3905            if dtype.upper() == "UNKNOWN":
3906                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3907
3908            try:
3909                data_type_exp = parse_one(
3910                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3911                )
3912            except ParseError:
3913                if udt:
3914                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3915                raise
3916        elif isinstance(dtype, DataType.Type):
3917            data_type_exp = DataType(this=dtype)
3918        elif isinstance(dtype, DataType):
3919            return maybe_copy(dtype, copy)
3920        else:
3921            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3922
3923        return DataType(**{**data_type_exp.args, **kwargs})
3924
3925    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3926        """
3927        Checks whether this DataType matches one of the provided data types. Nested types or precision
3928        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3929
3930        Args:
3931            dtypes: the data types to compare this DataType to.
3932
3933        Returns:
3934            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3935        """
3936        for dtype in dtypes:
3937            other = DataType.build(dtype, copy=False, udt=True)
3938
3939            if (
3940                other.expressions
3941                or self.this == DataType.Type.USERDEFINED
3942                or other.this == DataType.Type.USERDEFINED
3943            ):
3944                matches = self == other
3945            else:
3946                matches = self.this == other.this
3947
3948            if matches:
3949                return True
3950        return False
3951
3952
3953DATA_TYPE = t.Union[str, DataType, DataType.Type]
3954
3955
3956# https://www.postgresql.org/docs/15/datatype-pseudo.html
3957class PseudoType(DataType):
3958    arg_types = {"this": True}
3959
3960
3961# https://www.postgresql.org/docs/15/datatype-oid.html
3962class ObjectIdentifier(DataType):
3963    arg_types = {"this": True}
3964
3965
3966# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3967class SubqueryPredicate(Predicate):
3968    pass
3969
3970
3971class All(SubqueryPredicate):
3972    pass
3973
3974
3975class Any(SubqueryPredicate):
3976    pass
3977
3978
3979class Exists(SubqueryPredicate):
3980    pass
3981
3982
3983# Commands to interact with the databases or engines. For most of the command
3984# expressions we parse whatever comes after the command's name as a string.
3985class Command(Expression):
3986    arg_types = {"this": True, "expression": False}
3987
3988
3989class Transaction(Expression):
3990    arg_types = {"this": False, "modes": False, "mark": False}
3991
3992
3993class Commit(Expression):
3994    arg_types = {"chain": False, "this": False, "durability": False}
3995
3996
3997class Rollback(Expression):
3998    arg_types = {"savepoint": False, "this": False}
3999
4000
4001class AlterTable(Expression):
4002    arg_types = {
4003        "this": True,
4004        "actions": True,
4005        "exists": False,
4006        "only": False,
4007        "options": False,
4008    }
4009
4010
4011class AddConstraint(Expression):
4012    arg_types = {"expressions": True}
4013
4014
4015class DropPartition(Expression):
4016    arg_types = {"expressions": True, "exists": False}
4017
4018
4019# Binary expressions like (ADD a b)
4020class Binary(Condition):
4021    arg_types = {"this": True, "expression": True}
4022
4023    @property
4024    def left(self) -> Expression:
4025        return self.this
4026
4027    @property
4028    def right(self) -> Expression:
4029        return self.expression
4030
4031
4032class Add(Binary):
4033    pass
4034
4035
4036class Connector(Binary):
4037    pass
4038
4039
4040class And(Connector):
4041    pass
4042
4043
4044class Or(Connector):
4045    pass
4046
4047
4048class BitwiseAnd(Binary):
4049    pass
4050
4051
4052class BitwiseLeftShift(Binary):
4053    pass
4054
4055
4056class BitwiseOr(Binary):
4057    pass
4058
4059
4060class BitwiseRightShift(Binary):
4061    pass
4062
4063
4064class BitwiseXor(Binary):
4065    pass
4066
4067
4068class Div(Binary):
4069    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4070
4071
4072class Overlaps(Binary):
4073    pass
4074
4075
4076class Dot(Binary):
4077    @property
4078    def is_star(self) -> bool:
4079        return self.expression.is_star
4080
4081    @property
4082    def name(self) -> str:
4083        return self.expression.name
4084
4085    @property
4086    def output_name(self) -> str:
4087        return self.name
4088
4089    @classmethod
4090    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4091        """Build a Dot object with a sequence of expressions."""
4092        if len(expressions) < 2:
4093            raise ValueError("Dot requires >= 2 expressions.")
4094
4095        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4096
4097    @property
4098    def parts(self) -> t.List[Expression]:
4099        """Return the parts of a table / column in order catalog, db, table."""
4100        this, *parts = self.flatten()
4101
4102        parts.reverse()
4103
4104        for arg in ("this", "table", "db", "catalog"):
4105            part = this.args.get(arg)
4106
4107            if isinstance(part, Expression):
4108                parts.append(part)
4109
4110        parts.reverse()
4111        return parts
4112
4113
4114class DPipe(Binary):
4115    arg_types = {"this": True, "expression": True, "safe": False}
4116
4117
4118class EQ(Binary, Predicate):
4119    pass
4120
4121
4122class NullSafeEQ(Binary, Predicate):
4123    pass
4124
4125
4126class NullSafeNEQ(Binary, Predicate):
4127    pass
4128
4129
4130# Represents e.g. := in DuckDB which is mostly used for setting parameters
4131class PropertyEQ(Binary):
4132    pass
4133
4134
4135class Distance(Binary):
4136    pass
4137
4138
4139class Escape(Binary):
4140    pass
4141
4142
4143class Glob(Binary, Predicate):
4144    pass
4145
4146
4147class GT(Binary, Predicate):
4148    pass
4149
4150
4151class GTE(Binary, Predicate):
4152    pass
4153
4154
4155class ILike(Binary, Predicate):
4156    pass
4157
4158
4159class ILikeAny(Binary, Predicate):
4160    pass
4161
4162
4163class IntDiv(Binary):
4164    pass
4165
4166
4167class Is(Binary, Predicate):
4168    pass
4169
4170
4171class Kwarg(Binary):
4172    """Kwarg in special functions like func(kwarg => y)."""
4173
4174
4175class Like(Binary, Predicate):
4176    pass
4177
4178
4179class LikeAny(Binary, Predicate):
4180    pass
4181
4182
4183class LT(Binary, Predicate):
4184    pass
4185
4186
4187class LTE(Binary, Predicate):
4188    pass
4189
4190
4191class Mod(Binary):
4192    pass
4193
4194
4195class Mul(Binary):
4196    pass
4197
4198
4199class NEQ(Binary, Predicate):
4200    pass
4201
4202
4203# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4204class Operator(Binary):
4205    arg_types = {"this": True, "operator": True, "expression": True}
4206
4207
4208class SimilarTo(Binary, Predicate):
4209    pass
4210
4211
4212class Slice(Binary):
4213    arg_types = {"this": False, "expression": False}
4214
4215
4216class Sub(Binary):
4217    pass
4218
4219
4220# Unary Expressions
4221# (NOT a)
4222class Unary(Condition):
4223    pass
4224
4225
4226class BitwiseNot(Unary):
4227    pass
4228
4229
4230class Not(Unary):
4231    pass
4232
4233
4234class Paren(Unary):
4235    arg_types = {"this": True, "with": False}
4236
4237    @property
4238    def output_name(self) -> str:
4239        return self.this.name
4240
4241
4242class Neg(Unary):
4243    pass
4244
4245
4246class Alias(Expression):
4247    arg_types = {"this": True, "alias": False}
4248
4249    @property
4250    def output_name(self) -> str:
4251        return self.alias
4252
4253
4254# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4255# other dialects require identifiers. This enables us to transpile between them easily.
4256class PivotAlias(Alias):
4257    pass
4258
4259
4260class Aliases(Expression):
4261    arg_types = {"this": True, "expressions": True}
4262
4263    @property
4264    def aliases(self):
4265        return self.expressions
4266
4267
4268# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4269class AtIndex(Expression):
4270    arg_types = {"this": True, "expression": True}
4271
4272
4273class AtTimeZone(Expression):
4274    arg_types = {"this": True, "zone": True}
4275
4276
4277class FromTimeZone(Expression):
4278    arg_types = {"this": True, "zone": True}
4279
4280
4281class Between(Predicate):
4282    arg_types = {"this": True, "low": True, "high": True}
4283
4284
4285class Bracket(Condition):
4286    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4287    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4288
4289    @property
4290    def output_name(self) -> str:
4291        if len(self.expressions) == 1:
4292            return self.expressions[0].output_name
4293
4294        return super().output_name
4295
4296
4297class Distinct(Expression):
4298    arg_types = {"expressions": False, "on": False}
4299
4300
4301class In(Predicate):
4302    arg_types = {
4303        "this": True,
4304        "expressions": False,
4305        "query": False,
4306        "unnest": False,
4307        "field": False,
4308        "is_global": False,
4309    }
4310
4311
4312# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4313class ForIn(Expression):
4314    arg_types = {"this": True, "expression": True}
4315
4316
4317class TimeUnit(Expression):
4318    """Automatically converts unit arg into a var."""
4319
4320    arg_types = {"unit": False}
4321
4322    UNABBREVIATED_UNIT_NAME = {
4323        "D": "DAY",
4324        "H": "HOUR",
4325        "M": "MINUTE",
4326        "MS": "MILLISECOND",
4327        "NS": "NANOSECOND",
4328        "Q": "QUARTER",
4329        "S": "SECOND",
4330        "US": "MICROSECOND",
4331        "W": "WEEK",
4332        "Y": "YEAR",
4333    }
4334
4335    VAR_LIKE = (Column, Literal, Var)
4336
4337    def __init__(self, **args):
4338        unit = args.get("unit")
4339        if isinstance(unit, self.VAR_LIKE):
4340            args["unit"] = Var(
4341                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4342            )
4343        elif isinstance(unit, Week):
4344            unit.set("this", Var(this=unit.this.name.upper()))
4345
4346        super().__init__(**args)
4347
4348    @property
4349    def unit(self) -> t.Optional[Var]:
4350        return self.args.get("unit")
4351
4352
4353class IntervalOp(TimeUnit):
4354    arg_types = {"unit": True, "expression": True}
4355
4356    def interval(self):
4357        return Interval(
4358            this=self.expression.copy(),
4359            unit=self.unit.copy(),
4360        )
4361
4362
4363# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4364# https://trino.io/docs/current/language/types.html#interval-day-to-second
4365# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4366class IntervalSpan(DataType):
4367    arg_types = {"this": True, "expression": True}
4368
4369
4370class Interval(TimeUnit):
4371    arg_types = {"this": False, "unit": False}
4372
4373
4374class IgnoreNulls(Expression):
4375    pass
4376
4377
4378class RespectNulls(Expression):
4379    pass
4380
4381
4382# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4383class HavingMax(Expression):
4384    arg_types = {"this": True, "expression": True, "max": True}
4385
4386
4387# Functions
4388class Func(Condition):
4389    """
4390    The base class for all function expressions.
4391
4392    Attributes:
4393        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4394            treated as a variable length argument and the argument's value will be stored as a list.
4395        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4396            function expression. These values are used to map this node to a name during parsing as
4397            well as to provide the function's name during SQL string generation. By default the SQL
4398            name is set to the expression's class name transformed to snake case.
4399    """
4400
4401    is_var_len_args = False
4402
4403    @classmethod
4404    def from_arg_list(cls, args):
4405        if cls.is_var_len_args:
4406            all_arg_keys = list(cls.arg_types)
4407            # If this function supports variable length argument treat the last argument as such.
4408            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4409            num_non_var = len(non_var_len_arg_keys)
4410
4411            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4412            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4413        else:
4414            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4415
4416        return cls(**args_dict)
4417
4418    @classmethod
4419    def sql_names(cls):
4420        if cls is Func:
4421            raise NotImplementedError(
4422                "SQL name is only supported by concrete function implementations"
4423            )
4424        if "_sql_names" not in cls.__dict__:
4425            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4426        return cls._sql_names
4427
4428    @classmethod
4429    def sql_name(cls):
4430        return cls.sql_names()[0]
4431
4432    @classmethod
4433    def default_parser_mappings(cls):
4434        return {name: cls.from_arg_list for name in cls.sql_names()}
4435
4436
4437class AggFunc(Func):
4438    pass
4439
4440
4441class ParameterizedAgg(AggFunc):
4442    arg_types = {"this": True, "expressions": True, "params": True}
4443
4444
4445class Abs(Func):
4446    pass
4447
4448
4449class ArgMax(AggFunc):
4450    arg_types = {"this": True, "expression": True, "count": False}
4451    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4452
4453
4454class ArgMin(AggFunc):
4455    arg_types = {"this": True, "expression": True, "count": False}
4456    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4457
4458
4459class ApproxTopK(AggFunc):
4460    arg_types = {"this": True, "expression": False, "counters": False}
4461
4462
4463class Flatten(Func):
4464    pass
4465
4466
4467# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4468class Transform(Func):
4469    arg_types = {"this": True, "expression": True}
4470
4471
4472class Anonymous(Func):
4473    arg_types = {"this": True, "expressions": False}
4474    is_var_len_args = True
4475
4476    @property
4477    def name(self) -> str:
4478        return self.this if isinstance(self.this, str) else self.this.name
4479
4480
4481class AnonymousAggFunc(AggFunc):
4482    arg_types = {"this": True, "expressions": False}
4483    is_var_len_args = True
4484
4485
4486# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4487class CombinedAggFunc(AnonymousAggFunc):
4488    arg_types = {"this": True, "expressions": False, "parts": True}
4489
4490
4491class CombinedParameterizedAgg(ParameterizedAgg):
4492    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4493
4494
4495# https://docs.snowflake.com/en/sql-reference/functions/hll
4496# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4497class Hll(AggFunc):
4498    arg_types = {"this": True, "expressions": False}
4499    is_var_len_args = True
4500
4501
4502class ApproxDistinct(AggFunc):
4503    arg_types = {"this": True, "accuracy": False}
4504    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4505
4506
4507class Array(Func):
4508    arg_types = {"expressions": False}
4509    is_var_len_args = True
4510
4511
4512# https://docs.snowflake.com/en/sql-reference/functions/to_array
4513class ToArray(Func):
4514    pass
4515
4516
4517# https://docs.snowflake.com/en/sql-reference/functions/to_char
4518# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4519class ToChar(Func):
4520    arg_types = {"this": True, "format": False, "nlsparam": False}
4521
4522
4523# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4524class Convert(Func):
4525    arg_types = {"this": True, "expression": True, "style": False}
4526
4527
4528class GenerateSeries(Func):
4529    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4530
4531
4532class ArrayAgg(AggFunc):
4533    pass
4534
4535
4536class ArrayUniqueAgg(AggFunc):
4537    pass
4538
4539
4540class ArrayAll(Func):
4541    arg_types = {"this": True, "expression": True}
4542
4543
4544# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4545class ArrayAny(Func):
4546    arg_types = {"this": True, "expression": True}
4547
4548
4549class ArrayConcat(Func):
4550    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4551    arg_types = {"this": True, "expressions": False}
4552    is_var_len_args = True
4553
4554
4555class ArrayContains(Binary, Func):
4556    pass
4557
4558
4559class ArrayContained(Binary):
4560    pass
4561
4562
4563class ArrayFilter(Func):
4564    arg_types = {"this": True, "expression": True}
4565    _sql_names = ["FILTER", "ARRAY_FILTER"]
4566
4567
4568class ArrayJoin(Func):
4569    arg_types = {"this": True, "expression": True, "null": False}
4570    _sql_names = ["ARRAY_JOIN", "ARRAY_TO_STRING"]
4571
4572
4573class ArrayOverlaps(Binary, Func):
4574    pass
4575
4576
4577class ArraySize(Func):
4578    arg_types = {"this": True, "expression": False}
4579    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4580
4581
4582class ArraySort(Func):
4583    arg_types = {"this": True, "expression": False}
4584
4585
4586class ArraySum(Func):
4587    arg_types = {"this": True, "expression": False}
4588
4589
4590class ArrayUnionAgg(AggFunc):
4591    pass
4592
4593
4594class Avg(AggFunc):
4595    pass
4596
4597
4598class AnyValue(AggFunc):
4599    pass
4600
4601
4602class Lag(AggFunc):
4603    arg_types = {"this": True, "offset": False, "default": False}
4604
4605
4606class Lead(AggFunc):
4607    arg_types = {"this": True, "offset": False, "default": False}
4608
4609
4610# some dialects have a distinction between first and first_value, usually first is an aggregate func
4611# and first_value is a window func
4612class First(AggFunc):
4613    pass
4614
4615
4616class Last(AggFunc):
4617    pass
4618
4619
4620class FirstValue(AggFunc):
4621    pass
4622
4623
4624class LastValue(AggFunc):
4625    pass
4626
4627
4628class NthValue(AggFunc):
4629    arg_types = {"this": True, "offset": True}
4630
4631
4632class Case(Func):
4633    arg_types = {"this": False, "ifs": True, "default": False}
4634
4635    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4636        instance = maybe_copy(self, copy)
4637        instance.append(
4638            "ifs",
4639            If(
4640                this=maybe_parse(condition, copy=copy, **opts),
4641                true=maybe_parse(then, copy=copy, **opts),
4642            ),
4643        )
4644        return instance
4645
4646    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4647        instance = maybe_copy(self, copy)
4648        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4649        return instance
4650
4651
4652class Cast(Func):
4653    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4654
4655    @property
4656    def name(self) -> str:
4657        return self.this.name
4658
4659    @property
4660    def to(self) -> DataType:
4661        return self.args["to"]
4662
4663    @property
4664    def output_name(self) -> str:
4665        return self.name
4666
4667    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4668        """
4669        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4670        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4671        array<int> != array<float>.
4672
4673        Args:
4674            dtypes: the data types to compare this Cast's DataType to.
4675
4676        Returns:
4677            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4678        """
4679        return self.to.is_type(*dtypes)
4680
4681
4682class TryCast(Cast):
4683    pass
4684
4685
4686class CastToStrType(Func):
4687    arg_types = {"this": True, "to": True}
4688
4689
4690class Collate(Binary, Func):
4691    pass
4692
4693
4694class Ceil(Func):
4695    arg_types = {"this": True, "decimals": False}
4696    _sql_names = ["CEIL", "CEILING"]
4697
4698
4699class Coalesce(Func):
4700    arg_types = {"this": True, "expressions": False}
4701    is_var_len_args = True
4702    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4703
4704
4705class Chr(Func):
4706    arg_types = {"this": True, "charset": False, "expressions": False}
4707    is_var_len_args = True
4708    _sql_names = ["CHR", "CHAR"]
4709
4710
4711class Concat(Func):
4712    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4713    is_var_len_args = True
4714
4715
4716class ConcatWs(Concat):
4717    _sql_names = ["CONCAT_WS"]
4718
4719
4720# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4721class ConnectByRoot(Func):
4722    pass
4723
4724
4725class Count(AggFunc):
4726    arg_types = {"this": False, "expressions": False}
4727    is_var_len_args = True
4728
4729
4730class CountIf(AggFunc):
4731    _sql_names = ["COUNT_IF", "COUNTIF"]
4732
4733
4734# cube root
4735class Cbrt(Func):
4736    pass
4737
4738
4739class CurrentDate(Func):
4740    arg_types = {"this": False}
4741
4742
4743class CurrentDatetime(Func):
4744    arg_types = {"this": False}
4745
4746
4747class CurrentTime(Func):
4748    arg_types = {"this": False}
4749
4750
4751class CurrentTimestamp(Func):
4752    arg_types = {"this": False, "transaction": False}
4753
4754
4755class CurrentUser(Func):
4756    arg_types = {"this": False}
4757
4758
4759class DateAdd(Func, IntervalOp):
4760    arg_types = {"this": True, "expression": True, "unit": False}
4761
4762
4763class DateSub(Func, IntervalOp):
4764    arg_types = {"this": True, "expression": True, "unit": False}
4765
4766
4767class DateDiff(Func, TimeUnit):
4768    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4769    arg_types = {"this": True, "expression": True, "unit": False}
4770
4771
4772class DateTrunc(Func):
4773    arg_types = {"unit": True, "this": True, "zone": False}
4774
4775    def __init__(self, **args):
4776        unit = args.get("unit")
4777        if isinstance(unit, TimeUnit.VAR_LIKE):
4778            args["unit"] = Literal.string(
4779                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4780            )
4781        elif isinstance(unit, Week):
4782            unit.set("this", Literal.string(unit.this.name.upper()))
4783
4784        super().__init__(**args)
4785
4786    @property
4787    def unit(self) -> Expression:
4788        return self.args["unit"]
4789
4790
4791class DatetimeAdd(Func, IntervalOp):
4792    arg_types = {"this": True, "expression": True, "unit": False}
4793
4794
4795class DatetimeSub(Func, IntervalOp):
4796    arg_types = {"this": True, "expression": True, "unit": False}
4797
4798
4799class DatetimeDiff(Func, TimeUnit):
4800    arg_types = {"this": True, "expression": True, "unit": False}
4801
4802
4803class DatetimeTrunc(Func, TimeUnit):
4804    arg_types = {"this": True, "unit": True, "zone": False}
4805
4806
4807class DayOfWeek(Func):
4808    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4809
4810
4811class DayOfMonth(Func):
4812    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4813
4814
4815class DayOfYear(Func):
4816    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4817
4818
4819class ToDays(Func):
4820    pass
4821
4822
4823class WeekOfYear(Func):
4824    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4825
4826
4827class MonthsBetween(Func):
4828    arg_types = {"this": True, "expression": True, "roundoff": False}
4829
4830
4831class LastDay(Func, TimeUnit):
4832    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4833    arg_types = {"this": True, "unit": False}
4834
4835
4836class Extract(Func):
4837    arg_types = {"this": True, "expression": True}
4838
4839
4840class Timestamp(Func):
4841    arg_types = {"this": False, "expression": False, "with_tz": False}
4842
4843
4844class TimestampAdd(Func, TimeUnit):
4845    arg_types = {"this": True, "expression": True, "unit": False}
4846
4847
4848class TimestampSub(Func, TimeUnit):
4849    arg_types = {"this": True, "expression": True, "unit": False}
4850
4851
4852class TimestampDiff(Func, TimeUnit):
4853    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4854    arg_types = {"this": True, "expression": True, "unit": False}
4855
4856
4857class TimestampTrunc(Func, TimeUnit):
4858    arg_types = {"this": True, "unit": True, "zone": False}
4859
4860
4861class TimeAdd(Func, TimeUnit):
4862    arg_types = {"this": True, "expression": True, "unit": False}
4863
4864
4865class TimeSub(Func, TimeUnit):
4866    arg_types = {"this": True, "expression": True, "unit": False}
4867
4868
4869class TimeDiff(Func, TimeUnit):
4870    arg_types = {"this": True, "expression": True, "unit": False}
4871
4872
4873class TimeTrunc(Func, TimeUnit):
4874    arg_types = {"this": True, "unit": True, "zone": False}
4875
4876
4877class DateFromParts(Func):
4878    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4879    arg_types = {"year": True, "month": True, "day": True}
4880
4881
4882class TimeFromParts(Func):
4883    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4884    arg_types = {
4885        "hour": True,
4886        "min": True,
4887        "sec": True,
4888        "nano": False,
4889        "fractions": False,
4890        "precision": False,
4891    }
4892
4893
4894class DateStrToDate(Func):
4895    pass
4896
4897
4898class DateToDateStr(Func):
4899    pass
4900
4901
4902class DateToDi(Func):
4903    pass
4904
4905
4906# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4907class Date(Func):
4908    arg_types = {"this": False, "zone": False, "expressions": False}
4909    is_var_len_args = True
4910
4911
4912class Day(Func):
4913    pass
4914
4915
4916class Decode(Func):
4917    arg_types = {"this": True, "charset": True, "replace": False}
4918
4919
4920class DiToDate(Func):
4921    pass
4922
4923
4924class Encode(Func):
4925    arg_types = {"this": True, "charset": True}
4926
4927
4928class Exp(Func):
4929    pass
4930
4931
4932# https://docs.snowflake.com/en/sql-reference/functions/flatten
4933class Explode(Func):
4934    arg_types = {"this": True, "expressions": False}
4935    is_var_len_args = True
4936
4937
4938class ExplodeOuter(Explode):
4939    pass
4940
4941
4942class Posexplode(Explode):
4943    pass
4944
4945
4946class PosexplodeOuter(Posexplode, ExplodeOuter):
4947    pass
4948
4949
4950class Floor(Func):
4951    arg_types = {"this": True, "decimals": False}
4952
4953
4954class FromBase64(Func):
4955    pass
4956
4957
4958class ToBase64(Func):
4959    pass
4960
4961
4962class Greatest(Func):
4963    arg_types = {"this": True, "expressions": False}
4964    is_var_len_args = True
4965
4966
4967class GroupConcat(AggFunc):
4968    arg_types = {"this": True, "separator": False}
4969
4970
4971class Hex(Func):
4972    pass
4973
4974
4975class Xor(Connector, Func):
4976    arg_types = {"this": False, "expression": False, "expressions": False}
4977
4978
4979class If(Func):
4980    arg_types = {"this": True, "true": True, "false": False}
4981    _sql_names = ["IF", "IIF"]
4982
4983
4984class Nullif(Func):
4985    arg_types = {"this": True, "expression": True}
4986
4987
4988class Initcap(Func):
4989    arg_types = {"this": True, "expression": False}
4990
4991
4992class IsNan(Func):
4993    _sql_names = ["IS_NAN", "ISNAN"]
4994
4995
4996class IsInf(Func):
4997    _sql_names = ["IS_INF", "ISINF"]
4998
4999
5000class JSONPath(Expression):
5001    arg_types = {"expressions": True}
5002
5003    @property
5004    def output_name(self) -> str:
5005        last_segment = self.expressions[-1].this
5006        return last_segment if isinstance(last_segment, str) else ""
5007
5008
5009class JSONPathPart(Expression):
5010    arg_types = {}
5011
5012
5013class JSONPathFilter(JSONPathPart):
5014    arg_types = {"this": True}
5015
5016
5017class JSONPathKey(JSONPathPart):
5018    arg_types = {"this": True}
5019
5020
5021class JSONPathRecursive(JSONPathPart):
5022    arg_types = {"this": False}
5023
5024
5025class JSONPathRoot(JSONPathPart):
5026    pass
5027
5028
5029class JSONPathScript(JSONPathPart):
5030    arg_types = {"this": True}
5031
5032
5033class JSONPathSlice(JSONPathPart):
5034    arg_types = {"start": False, "end": False, "step": False}
5035
5036
5037class JSONPathSelector(JSONPathPart):
5038    arg_types = {"this": True}
5039
5040
5041class JSONPathSubscript(JSONPathPart):
5042    arg_types = {"this": True}
5043
5044
5045class JSONPathUnion(JSONPathPart):
5046    arg_types = {"expressions": True}
5047
5048
5049class JSONPathWildcard(JSONPathPart):
5050    pass
5051
5052
5053class FormatJson(Expression):
5054    pass
5055
5056
5057class JSONKeyValue(Expression):
5058    arg_types = {"this": True, "expression": True}
5059
5060
5061class JSONObject(Func):
5062    arg_types = {
5063        "expressions": False,
5064        "null_handling": False,
5065        "unique_keys": False,
5066        "return_type": False,
5067        "encoding": False,
5068    }
5069
5070
5071class JSONObjectAgg(AggFunc):
5072    arg_types = {
5073        "expressions": False,
5074        "null_handling": False,
5075        "unique_keys": False,
5076        "return_type": False,
5077        "encoding": False,
5078    }
5079
5080
5081# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5082class JSONArray(Func):
5083    arg_types = {
5084        "expressions": True,
5085        "null_handling": False,
5086        "return_type": False,
5087        "strict": False,
5088    }
5089
5090
5091# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5092class JSONArrayAgg(Func):
5093    arg_types = {
5094        "this": True,
5095        "order": False,
5096        "null_handling": False,
5097        "return_type": False,
5098        "strict": False,
5099    }
5100
5101
5102# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5103# Note: parsing of JSON column definitions is currently incomplete.
5104class JSONColumnDef(Expression):
5105    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5106
5107
5108class JSONSchema(Expression):
5109    arg_types = {"expressions": True}
5110
5111
5112# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5113class JSONTable(Func):
5114    arg_types = {
5115        "this": True,
5116        "schema": True,
5117        "path": False,
5118        "error_handling": False,
5119        "empty_handling": False,
5120    }
5121
5122
5123class OpenJSONColumnDef(Expression):
5124    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5125
5126
5127class OpenJSON(Func):
5128    arg_types = {"this": True, "path": False, "expressions": False}
5129
5130
5131class JSONBContains(Binary):
5132    _sql_names = ["JSONB_CONTAINS"]
5133
5134
5135class JSONExtract(Binary, Func):
5136    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5137    _sql_names = ["JSON_EXTRACT"]
5138    is_var_len_args = True
5139
5140    @property
5141    def output_name(self) -> str:
5142        return self.expression.output_name if not self.expressions else ""
5143
5144
5145class JSONExtractScalar(Binary, Func):
5146    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5147    _sql_names = ["JSON_EXTRACT_SCALAR"]
5148    is_var_len_args = True
5149
5150    @property
5151    def output_name(self) -> str:
5152        return self.expression.output_name
5153
5154
5155class JSONBExtract(Binary, Func):
5156    _sql_names = ["JSONB_EXTRACT"]
5157
5158
5159class JSONBExtractScalar(Binary, Func):
5160    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5161
5162
5163class JSONFormat(Func):
5164    arg_types = {"this": False, "options": False}
5165    _sql_names = ["JSON_FORMAT"]
5166
5167
5168# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5169class JSONArrayContains(Binary, Predicate, Func):
5170    _sql_names = ["JSON_ARRAY_CONTAINS"]
5171
5172
5173class ParseJSON(Func):
5174    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5175    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5176    arg_types = {"this": True, "expressions": False}
5177    is_var_len_args = True
5178
5179
5180class Least(Func):
5181    arg_types = {"this": True, "expressions": False}
5182    is_var_len_args = True
5183
5184
5185class Left(Func):
5186    arg_types = {"this": True, "expression": True}
5187
5188
5189class Right(Func):
5190    arg_types = {"this": True, "expression": True}
5191
5192
5193class Length(Func):
5194    _sql_names = ["LENGTH", "LEN"]
5195
5196
5197class Levenshtein(Func):
5198    arg_types = {
5199        "this": True,
5200        "expression": False,
5201        "ins_cost": False,
5202        "del_cost": False,
5203        "sub_cost": False,
5204    }
5205
5206
5207class Ln(Func):
5208    pass
5209
5210
5211class Log(Func):
5212    arg_types = {"this": True, "expression": False}
5213
5214
5215class Log2(Func):
5216    pass
5217
5218
5219class Log10(Func):
5220    pass
5221
5222
5223class LogicalOr(AggFunc):
5224    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5225
5226
5227class LogicalAnd(AggFunc):
5228    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5229
5230
5231class Lower(Func):
5232    _sql_names = ["LOWER", "LCASE"]
5233
5234
5235class Map(Func):
5236    arg_types = {"keys": False, "values": False}
5237
5238    @property
5239    def keys(self) -> t.List[Expression]:
5240        keys = self.args.get("keys")
5241        return keys.expressions if keys else []
5242
5243    @property
5244    def values(self) -> t.List[Expression]:
5245        values = self.args.get("values")
5246        return values.expressions if values else []
5247
5248
5249class MapFromEntries(Func):
5250    pass
5251
5252
5253class StarMap(Func):
5254    pass
5255
5256
5257class VarMap(Func):
5258    arg_types = {"keys": True, "values": True}
5259    is_var_len_args = True
5260
5261    @property
5262    def keys(self) -> t.List[Expression]:
5263        return self.args["keys"].expressions
5264
5265    @property
5266    def values(self) -> t.List[Expression]:
5267        return self.args["values"].expressions
5268
5269
5270# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5271class MatchAgainst(Func):
5272    arg_types = {"this": True, "expressions": True, "modifier": False}
5273
5274
5275class Max(AggFunc):
5276    arg_types = {"this": True, "expressions": False}
5277    is_var_len_args = True
5278
5279
5280class MD5(Func):
5281    _sql_names = ["MD5"]
5282
5283
5284# Represents the variant of the MD5 function that returns a binary value
5285class MD5Digest(Func):
5286    _sql_names = ["MD5_DIGEST"]
5287
5288
5289class Min(AggFunc):
5290    arg_types = {"this": True, "expressions": False}
5291    is_var_len_args = True
5292
5293
5294class Month(Func):
5295    pass
5296
5297
5298class AddMonths(Func):
5299    arg_types = {"this": True, "expression": True}
5300
5301
5302class Nvl2(Func):
5303    arg_types = {"this": True, "true": True, "false": False}
5304
5305
5306# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5307class Predict(Func):
5308    arg_types = {"this": True, "expression": True, "params_struct": False}
5309
5310
5311class Pow(Binary, Func):
5312    _sql_names = ["POWER", "POW"]
5313
5314
5315class PercentileCont(AggFunc):
5316    arg_types = {"this": True, "expression": False}
5317
5318
5319class PercentileDisc(AggFunc):
5320    arg_types = {"this": True, "expression": False}
5321
5322
5323class Quantile(AggFunc):
5324    arg_types = {"this": True, "quantile": True}
5325
5326
5327class ApproxQuantile(Quantile):
5328    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5329
5330
5331class Rand(Func):
5332    _sql_names = ["RAND", "RANDOM"]
5333    arg_types = {"this": False}
5334
5335
5336class Randn(Func):
5337    arg_types = {"this": False}
5338
5339
5340class RangeN(Func):
5341    arg_types = {"this": True, "expressions": True, "each": False}
5342
5343
5344class ReadCSV(Func):
5345    _sql_names = ["READ_CSV"]
5346    is_var_len_args = True
5347    arg_types = {"this": True, "expressions": False}
5348
5349
5350class Reduce(Func):
5351    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5352
5353
5354class RegexpExtract(Func):
5355    arg_types = {
5356        "this": True,
5357        "expression": True,
5358        "position": False,
5359        "occurrence": False,
5360        "parameters": False,
5361        "group": False,
5362    }
5363
5364
5365class RegexpReplace(Func):
5366    arg_types = {
5367        "this": True,
5368        "expression": True,
5369        "replacement": False,
5370        "position": False,
5371        "occurrence": False,
5372        "parameters": False,
5373        "modifiers": False,
5374    }
5375
5376
5377class RegexpLike(Binary, Func):
5378    arg_types = {"this": True, "expression": True, "flag": False}
5379
5380
5381class RegexpILike(Binary, Func):
5382    arg_types = {"this": True, "expression": True, "flag": False}
5383
5384
5385# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5386# limit is the number of times a pattern is applied
5387class RegexpSplit(Func):
5388    arg_types = {"this": True, "expression": True, "limit": False}
5389
5390
5391class Repeat(Func):
5392    arg_types = {"this": True, "times": True}
5393
5394
5395# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5396# tsql third argument function == trunctaion if not 0
5397class Round(Func):
5398    arg_types = {"this": True, "decimals": False, "truncate": False}
5399
5400
5401class RowNumber(Func):
5402    arg_types: t.Dict[str, t.Any] = {}
5403
5404
5405class SafeDivide(Func):
5406    arg_types = {"this": True, "expression": True}
5407
5408
5409class SHA(Func):
5410    _sql_names = ["SHA", "SHA1"]
5411
5412
5413class SHA2(Func):
5414    _sql_names = ["SHA2"]
5415    arg_types = {"this": True, "length": False}
5416
5417
5418class Sign(Func):
5419    _sql_names = ["SIGN", "SIGNUM"]
5420
5421
5422class SortArray(Func):
5423    arg_types = {"this": True, "asc": False}
5424
5425
5426class Split(Func):
5427    arg_types = {"this": True, "expression": True, "limit": False}
5428
5429
5430# Start may be omitted in the case of postgres
5431# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5432class Substring(Func):
5433    arg_types = {"this": True, "start": False, "length": False}
5434
5435
5436class StandardHash(Func):
5437    arg_types = {"this": True, "expression": False}
5438
5439
5440class StartsWith(Func):
5441    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5442    arg_types = {"this": True, "expression": True}
5443
5444
5445class StrPosition(Func):
5446    arg_types = {
5447        "this": True,
5448        "substr": True,
5449        "position": False,
5450        "instance": False,
5451    }
5452
5453
5454class StrToDate(Func):
5455    arg_types = {"this": True, "format": True}
5456
5457
5458class StrToTime(Func):
5459    arg_types = {"this": True, "format": True, "zone": False}
5460
5461
5462# Spark allows unix_timestamp()
5463# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5464class StrToUnix(Func):
5465    arg_types = {"this": False, "format": False}
5466
5467
5468# https://prestodb.io/docs/current/functions/string.html
5469# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5470class StrToMap(Func):
5471    arg_types = {
5472        "this": True,
5473        "pair_delim": False,
5474        "key_value_delim": False,
5475        "duplicate_resolution_callback": False,
5476    }
5477
5478
5479class NumberToStr(Func):
5480    arg_types = {"this": True, "format": True, "culture": False}
5481
5482
5483class FromBase(Func):
5484    arg_types = {"this": True, "expression": True}
5485
5486
5487class Struct(Func):
5488    arg_types = {"expressions": False}
5489    is_var_len_args = True
5490
5491
5492class StructExtract(Func):
5493    arg_types = {"this": True, "expression": True}
5494
5495
5496# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5497# https://docs.snowflake.com/en/sql-reference/functions/insert
5498class Stuff(Func):
5499    _sql_names = ["STUFF", "INSERT"]
5500    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5501
5502
5503class Sum(AggFunc):
5504    pass
5505
5506
5507class Sqrt(Func):
5508    pass
5509
5510
5511class Stddev(AggFunc):
5512    pass
5513
5514
5515class StddevPop(AggFunc):
5516    pass
5517
5518
5519class StddevSamp(AggFunc):
5520    pass
5521
5522
5523class TimeToStr(Func):
5524    arg_types = {"this": True, "format": True, "culture": False}
5525
5526
5527class TimeToTimeStr(Func):
5528    pass
5529
5530
5531class TimeToUnix(Func):
5532    pass
5533
5534
5535class TimeStrToDate(Func):
5536    pass
5537
5538
5539class TimeStrToTime(Func):
5540    pass
5541
5542
5543class TimeStrToUnix(Func):
5544    pass
5545
5546
5547class Trim(Func):
5548    arg_types = {
5549        "this": True,
5550        "expression": False,
5551        "position": False,
5552        "collation": False,
5553    }
5554
5555
5556class TsOrDsAdd(Func, TimeUnit):
5557    # return_type is used to correctly cast the arguments of this expression when transpiling it
5558    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5559
5560    @property
5561    def return_type(self) -> DataType:
5562        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5563
5564
5565class TsOrDsDiff(Func, TimeUnit):
5566    arg_types = {"this": True, "expression": True, "unit": False}
5567
5568
5569class TsOrDsToDateStr(Func):
5570    pass
5571
5572
5573class TsOrDsToDate(Func):
5574    arg_types = {"this": True, "format": False}
5575
5576
5577class TsOrDsToTime(Func):
5578    pass
5579
5580
5581class TsOrDiToDi(Func):
5582    pass
5583
5584
5585class Unhex(Func):
5586    pass
5587
5588
5589# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5590class UnixDate(Func):
5591    pass
5592
5593
5594class UnixToStr(Func):
5595    arg_types = {"this": True, "format": False}
5596
5597
5598# https://prestodb.io/docs/current/functions/datetime.html
5599# presto has weird zone/hours/minutes
5600class UnixToTime(Func):
5601    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5602
5603    SECONDS = Literal.number(0)
5604    DECIS = Literal.number(1)
5605    CENTIS = Literal.number(2)
5606    MILLIS = Literal.number(3)
5607    DECIMILLIS = Literal.number(4)
5608    CENTIMILLIS = Literal.number(5)
5609    MICROS = Literal.number(6)
5610    DECIMICROS = Literal.number(7)
5611    CENTIMICROS = Literal.number(8)
5612    NANOS = Literal.number(9)
5613
5614
5615class UnixToTimeStr(Func):
5616    pass
5617
5618
5619class TimestampFromParts(Func):
5620    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5621    arg_types = {
5622        "year": True,
5623        "month": True,
5624        "day": True,
5625        "hour": True,
5626        "min": True,
5627        "sec": True,
5628        "nano": False,
5629        "zone": False,
5630        "milli": False,
5631    }
5632
5633
5634class Upper(Func):
5635    _sql_names = ["UPPER", "UCASE"]
5636
5637
5638class Variance(AggFunc):
5639    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5640
5641
5642class VariancePop(AggFunc):
5643    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5644
5645
5646class Week(Func):
5647    arg_types = {"this": True, "mode": False}
5648
5649
5650class XMLTable(Func):
5651    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5652
5653
5654class Year(Func):
5655    pass
5656
5657
5658class Use(Expression):
5659    arg_types = {"this": True, "kind": False}
5660
5661
5662class Merge(Expression):
5663    arg_types = {
5664        "this": True,
5665        "using": True,
5666        "on": True,
5667        "expressions": True,
5668        "with": False,
5669    }
5670
5671
5672class When(Func):
5673    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5674
5675
5676# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5677# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5678class NextValueFor(Func):
5679    arg_types = {"this": True, "order": False}
5680
5681
5682def _norm_arg(arg):
5683    return arg.lower() if type(arg) is str else arg
5684
5685
5686ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5687FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5688
5689JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5690
5691
5692# Helpers
5693@t.overload
5694def maybe_parse(
5695    sql_or_expression: ExpOrStr,
5696    *,
5697    into: t.Type[E],
5698    dialect: DialectType = None,
5699    prefix: t.Optional[str] = None,
5700    copy: bool = False,
5701    **opts,
5702) -> E: ...
5703
5704
5705@t.overload
5706def maybe_parse(
5707    sql_or_expression: str | E,
5708    *,
5709    into: t.Optional[IntoType] = None,
5710    dialect: DialectType = None,
5711    prefix: t.Optional[str] = None,
5712    copy: bool = False,
5713    **opts,
5714) -> E: ...
5715
5716
5717def maybe_parse(
5718    sql_or_expression: ExpOrStr,
5719    *,
5720    into: t.Optional[IntoType] = None,
5721    dialect: DialectType = None,
5722    prefix: t.Optional[str] = None,
5723    copy: bool = False,
5724    **opts,
5725) -> Expression:
5726    """Gracefully handle a possible string or expression.
5727
5728    Example:
5729        >>> maybe_parse("1")
5730        Literal(this=1, is_string=False)
5731        >>> maybe_parse(to_identifier("x"))
5732        Identifier(this=x, quoted=False)
5733
5734    Args:
5735        sql_or_expression: the SQL code string or an expression
5736        into: the SQLGlot Expression to parse into
5737        dialect: the dialect used to parse the input expressions (in the case that an
5738            input expression is a SQL string).
5739        prefix: a string to prefix the sql with before it gets parsed
5740            (automatically includes a space)
5741        copy: whether to copy the expression.
5742        **opts: other options to use to parse the input expressions (again, in the case
5743            that an input expression is a SQL string).
5744
5745    Returns:
5746        Expression: the parsed or given expression.
5747    """
5748    if isinstance(sql_or_expression, Expression):
5749        if copy:
5750            return sql_or_expression.copy()
5751        return sql_or_expression
5752
5753    if sql_or_expression is None:
5754        raise ParseError("SQL cannot be None")
5755
5756    import sqlglot
5757
5758    sql = str(sql_or_expression)
5759    if prefix:
5760        sql = f"{prefix} {sql}"
5761
5762    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5763
5764
5765@t.overload
5766def maybe_copy(instance: None, copy: bool = True) -> None: ...
5767
5768
5769@t.overload
5770def maybe_copy(instance: E, copy: bool = True) -> E: ...
5771
5772
5773def maybe_copy(instance, copy=True):
5774    return instance.copy() if copy and instance else instance
5775
5776
5777def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5778    """Generate a textual representation of an Expression tree"""
5779    indent = "\n" + ("  " * (level + 1))
5780    delim = f",{indent}"
5781
5782    if isinstance(node, Expression):
5783        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5784
5785        if (node.type or verbose) and not isinstance(node, DataType):
5786            args["_type"] = node.type
5787        if node.comments or verbose:
5788            args["_comments"] = node.comments
5789
5790        if verbose:
5791            args["_id"] = id(node)
5792
5793        # Inline leaves for a more compact representation
5794        if node.is_leaf():
5795            indent = ""
5796            delim = ", "
5797
5798        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5799        return f"{node.__class__.__name__}({indent}{items})"
5800
5801    if isinstance(node, list):
5802        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5803        items = f"{indent}{items}" if items else ""
5804        return f"[{items}]"
5805
5806    # Indent multiline strings to match the current level
5807    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5808
5809
5810def _is_wrong_expression(expression, into):
5811    return isinstance(expression, Expression) and not isinstance(expression, into)
5812
5813
5814def _apply_builder(
5815    expression,
5816    instance,
5817    arg,
5818    copy=True,
5819    prefix=None,
5820    into=None,
5821    dialect=None,
5822    into_arg="this",
5823    **opts,
5824):
5825    if _is_wrong_expression(expression, into):
5826        expression = into(**{into_arg: expression})
5827    instance = maybe_copy(instance, copy)
5828    expression = maybe_parse(
5829        sql_or_expression=expression,
5830        prefix=prefix,
5831        into=into,
5832        dialect=dialect,
5833        **opts,
5834    )
5835    instance.set(arg, expression)
5836    return instance
5837
5838
5839def _apply_child_list_builder(
5840    *expressions,
5841    instance,
5842    arg,
5843    append=True,
5844    copy=True,
5845    prefix=None,
5846    into=None,
5847    dialect=None,
5848    properties=None,
5849    **opts,
5850):
5851    instance = maybe_copy(instance, copy)
5852    parsed = []
5853    for expression in expressions:
5854        if expression is not None:
5855            if _is_wrong_expression(expression, into):
5856                expression = into(expressions=[expression])
5857
5858            expression = maybe_parse(
5859                expression,
5860                into=into,
5861                dialect=dialect,
5862                prefix=prefix,
5863                **opts,
5864            )
5865            parsed.extend(expression.expressions)
5866
5867    existing = instance.args.get(arg)
5868    if append and existing:
5869        parsed = existing.expressions + parsed
5870
5871    child = into(expressions=parsed)
5872    for k, v in (properties or {}).items():
5873        child.set(k, v)
5874    instance.set(arg, child)
5875
5876    return instance
5877
5878
5879def _apply_list_builder(
5880    *expressions,
5881    instance,
5882    arg,
5883    append=True,
5884    copy=True,
5885    prefix=None,
5886    into=None,
5887    dialect=None,
5888    **opts,
5889):
5890    inst = maybe_copy(instance, copy)
5891
5892    expressions = [
5893        maybe_parse(
5894            sql_or_expression=expression,
5895            into=into,
5896            prefix=prefix,
5897            dialect=dialect,
5898            **opts,
5899        )
5900        for expression in expressions
5901        if expression is not None
5902    ]
5903
5904    existing_expressions = inst.args.get(arg)
5905    if append and existing_expressions:
5906        expressions = existing_expressions + expressions
5907
5908    inst.set(arg, expressions)
5909    return inst
5910
5911
5912def _apply_conjunction_builder(
5913    *expressions,
5914    instance,
5915    arg,
5916    into=None,
5917    append=True,
5918    copy=True,
5919    dialect=None,
5920    **opts,
5921):
5922    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5923    if not expressions:
5924        return instance
5925
5926    inst = maybe_copy(instance, copy)
5927
5928    existing = inst.args.get(arg)
5929    if append and existing is not None:
5930        expressions = [existing.this if into else existing] + list(expressions)
5931
5932    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5933
5934    inst.set(arg, into(this=node) if into else node)
5935    return inst
5936
5937
5938def _apply_cte_builder(
5939    instance: E,
5940    alias: ExpOrStr,
5941    as_: ExpOrStr,
5942    recursive: t.Optional[bool] = None,
5943    append: bool = True,
5944    dialect: DialectType = None,
5945    copy: bool = True,
5946    **opts,
5947) -> E:
5948    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5949    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5950    cte = CTE(this=as_expression, alias=alias_expression)
5951    return _apply_child_list_builder(
5952        cte,
5953        instance=instance,
5954        arg="with",
5955        append=append,
5956        copy=copy,
5957        into=With,
5958        properties={"recursive": recursive or False},
5959    )
5960
5961
5962def _combine(
5963    expressions: t.Sequence[t.Optional[ExpOrStr]],
5964    operator: t.Type[Connector],
5965    dialect: DialectType = None,
5966    copy: bool = True,
5967    **opts,
5968) -> Expression:
5969    conditions = [
5970        condition(expression, dialect=dialect, copy=copy, **opts)
5971        for expression in expressions
5972        if expression is not None
5973    ]
5974
5975    this, *rest = conditions
5976    if rest:
5977        this = _wrap(this, Connector)
5978    for expression in rest:
5979        this = operator(this=this, expression=_wrap(expression, Connector))
5980
5981    return this
5982
5983
5984def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5985    return Paren(this=expression) if isinstance(expression, kind) else expression
5986
5987
5988def union(
5989    left: ExpOrStr,
5990    right: ExpOrStr,
5991    distinct: bool = True,
5992    dialect: DialectType = None,
5993    copy: bool = True,
5994    **opts,
5995) -> Union:
5996    """
5997    Initializes a syntax tree from one UNION expression.
5998
5999    Example:
6000        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6001        'SELECT * FROM foo UNION SELECT * FROM bla'
6002
6003    Args:
6004        left: the SQL code string corresponding to the left-hand side.
6005            If an `Expression` instance is passed, it will be used as-is.
6006        right: the SQL code string corresponding to the right-hand side.
6007            If an `Expression` instance is passed, it will be used as-is.
6008        distinct: set the DISTINCT flag if and only if this is true.
6009        dialect: the dialect used to parse the input expression.
6010        copy: whether to copy the expression.
6011        opts: other options to use to parse the input expressions.
6012
6013    Returns:
6014        The new Union instance.
6015    """
6016    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6017    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6018
6019    return Union(this=left, expression=right, distinct=distinct)
6020
6021
6022def intersect(
6023    left: ExpOrStr,
6024    right: ExpOrStr,
6025    distinct: bool = True,
6026    dialect: DialectType = None,
6027    copy: bool = True,
6028    **opts,
6029) -> Intersect:
6030    """
6031    Initializes a syntax tree from one INTERSECT expression.
6032
6033    Example:
6034        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6035        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6036
6037    Args:
6038        left: the SQL code string corresponding to the left-hand side.
6039            If an `Expression` instance is passed, it will be used as-is.
6040        right: the SQL code string corresponding to the right-hand side.
6041            If an `Expression` instance is passed, it will be used as-is.
6042        distinct: set the DISTINCT flag if and only if this is true.
6043        dialect: the dialect used to parse the input expression.
6044        copy: whether to copy the expression.
6045        opts: other options to use to parse the input expressions.
6046
6047    Returns:
6048        The new Intersect instance.
6049    """
6050    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6051    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6052
6053    return Intersect(this=left, expression=right, distinct=distinct)
6054
6055
6056def except_(
6057    left: ExpOrStr,
6058    right: ExpOrStr,
6059    distinct: bool = True,
6060    dialect: DialectType = None,
6061    copy: bool = True,
6062    **opts,
6063) -> Except:
6064    """
6065    Initializes a syntax tree from one EXCEPT expression.
6066
6067    Example:
6068        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6069        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6070
6071    Args:
6072        left: the SQL code string corresponding to the left-hand side.
6073            If an `Expression` instance is passed, it will be used as-is.
6074        right: the SQL code string corresponding to the right-hand side.
6075            If an `Expression` instance is passed, it will be used as-is.
6076        distinct: set the DISTINCT flag if and only if this is true.
6077        dialect: the dialect used to parse the input expression.
6078        copy: whether to copy the expression.
6079        opts: other options to use to parse the input expressions.
6080
6081    Returns:
6082        The new Except instance.
6083    """
6084    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6085    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6086
6087    return Except(this=left, expression=right, distinct=distinct)
6088
6089
6090def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6091    """
6092    Initializes a syntax tree from one or multiple SELECT expressions.
6093
6094    Example:
6095        >>> select("col1", "col2").from_("tbl").sql()
6096        'SELECT col1, col2 FROM tbl'
6097
6098    Args:
6099        *expressions: the SQL code string to parse as the expressions of a
6100            SELECT statement. If an Expression instance is passed, this is used as-is.
6101        dialect: the dialect used to parse the input expressions (in the case that an
6102            input expression is a SQL string).
6103        **opts: other options to use to parse the input expressions (again, in the case
6104            that an input expression is a SQL string).
6105
6106    Returns:
6107        Select: the syntax tree for the SELECT statement.
6108    """
6109    return Select().select(*expressions, dialect=dialect, **opts)
6110
6111
6112def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6113    """
6114    Initializes a syntax tree from a FROM expression.
6115
6116    Example:
6117        >>> from_("tbl").select("col1", "col2").sql()
6118        'SELECT col1, col2 FROM tbl'
6119
6120    Args:
6121        *expression: the SQL code string to parse as the FROM expressions of a
6122            SELECT statement. If an Expression instance is passed, this is used as-is.
6123        dialect: the dialect used to parse the input expression (in the case that the
6124            input expression is a SQL string).
6125        **opts: other options to use to parse the input expressions (again, in the case
6126            that the input expression is a SQL string).
6127
6128    Returns:
6129        Select: the syntax tree for the SELECT statement.
6130    """
6131    return Select().from_(expression, dialect=dialect, **opts)
6132
6133
6134def update(
6135    table: str | Table,
6136    properties: dict,
6137    where: t.Optional[ExpOrStr] = None,
6138    from_: t.Optional[ExpOrStr] = None,
6139    dialect: DialectType = None,
6140    **opts,
6141) -> Update:
6142    """
6143    Creates an update statement.
6144
6145    Example:
6146        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6147        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6148
6149    Args:
6150        *properties: dictionary of properties to set which are
6151            auto converted to sql objects eg None -> NULL
6152        where: sql conditional parsed into a WHERE statement
6153        from_: sql statement parsed into a FROM statement
6154        dialect: the dialect used to parse the input expressions.
6155        **opts: other options to use to parse the input expressions.
6156
6157    Returns:
6158        Update: the syntax tree for the UPDATE statement.
6159    """
6160    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6161    update_expr.set(
6162        "expressions",
6163        [
6164            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6165            for k, v in properties.items()
6166        ],
6167    )
6168    if from_:
6169        update_expr.set(
6170            "from",
6171            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6172        )
6173    if isinstance(where, Condition):
6174        where = Where(this=where)
6175    if where:
6176        update_expr.set(
6177            "where",
6178            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6179        )
6180    return update_expr
6181
6182
6183def delete(
6184    table: ExpOrStr,
6185    where: t.Optional[ExpOrStr] = None,
6186    returning: t.Optional[ExpOrStr] = None,
6187    dialect: DialectType = None,
6188    **opts,
6189) -> Delete:
6190    """
6191    Builds a delete statement.
6192
6193    Example:
6194        >>> delete("my_table", where="id > 1").sql()
6195        'DELETE FROM my_table WHERE id > 1'
6196
6197    Args:
6198        where: sql conditional parsed into a WHERE statement
6199        returning: sql conditional parsed into a RETURNING statement
6200        dialect: the dialect used to parse the input expressions.
6201        **opts: other options to use to parse the input expressions.
6202
6203    Returns:
6204        Delete: the syntax tree for the DELETE statement.
6205    """
6206    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6207    if where:
6208        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6209    if returning:
6210        delete_expr = t.cast(
6211            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6212        )
6213    return delete_expr
6214
6215
6216def insert(
6217    expression: ExpOrStr,
6218    into: ExpOrStr,
6219    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6220    overwrite: t.Optional[bool] = None,
6221    returning: t.Optional[ExpOrStr] = None,
6222    dialect: DialectType = None,
6223    copy: bool = True,
6224    **opts,
6225) -> Insert:
6226    """
6227    Builds an INSERT statement.
6228
6229    Example:
6230        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6231        'INSERT INTO tbl VALUES (1, 2, 3)'
6232
6233    Args:
6234        expression: the sql string or expression of the INSERT statement
6235        into: the tbl to insert data to.
6236        columns: optionally the table's column names.
6237        overwrite: whether to INSERT OVERWRITE or not.
6238        returning: sql conditional parsed into a RETURNING statement
6239        dialect: the dialect used to parse the input expressions.
6240        copy: whether to copy the expression.
6241        **opts: other options to use to parse the input expressions.
6242
6243    Returns:
6244        Insert: the syntax tree for the INSERT statement.
6245    """
6246    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6247    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6248
6249    if columns:
6250        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6251
6252    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6253
6254    if returning:
6255        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6256
6257    return insert
6258
6259
6260def condition(
6261    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6262) -> Condition:
6263    """
6264    Initialize a logical condition expression.
6265
6266    Example:
6267        >>> condition("x=1").sql()
6268        'x = 1'
6269
6270        This is helpful for composing larger logical syntax trees:
6271        >>> where = condition("x=1")
6272        >>> where = where.and_("y=1")
6273        >>> Select().from_("tbl").select("*").where(where).sql()
6274        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6275
6276    Args:
6277        *expression: the SQL code string to parse.
6278            If an Expression instance is passed, this is used as-is.
6279        dialect: the dialect used to parse the input expression (in the case that the
6280            input expression is a SQL string).
6281        copy: Whether to copy `expression` (only applies to expressions).
6282        **opts: other options to use to parse the input expressions (again, in the case
6283            that the input expression is a SQL string).
6284
6285    Returns:
6286        The new Condition instance
6287    """
6288    return maybe_parse(
6289        expression,
6290        into=Condition,
6291        dialect=dialect,
6292        copy=copy,
6293        **opts,
6294    )
6295
6296
6297def and_(
6298    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6299) -> Condition:
6300    """
6301    Combine multiple conditions with an AND logical operator.
6302
6303    Example:
6304        >>> and_("x=1", and_("y=1", "z=1")).sql()
6305        'x = 1 AND (y = 1 AND z = 1)'
6306
6307    Args:
6308        *expressions: the SQL code strings to parse.
6309            If an Expression instance is passed, this is used as-is.
6310        dialect: the dialect used to parse the input expression.
6311        copy: whether to copy `expressions` (only applies to Expressions).
6312        **opts: other options to use to parse the input expressions.
6313
6314    Returns:
6315        And: the new condition
6316    """
6317    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6318
6319
6320def or_(
6321    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6322) -> Condition:
6323    """
6324    Combine multiple conditions with an OR logical operator.
6325
6326    Example:
6327        >>> or_("x=1", or_("y=1", "z=1")).sql()
6328        'x = 1 OR (y = 1 OR z = 1)'
6329
6330    Args:
6331        *expressions: the SQL code strings to parse.
6332            If an Expression instance is passed, this is used as-is.
6333        dialect: the dialect used to parse the input expression.
6334        copy: whether to copy `expressions` (only applies to Expressions).
6335        **opts: other options to use to parse the input expressions.
6336
6337    Returns:
6338        Or: the new condition
6339    """
6340    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6341
6342
6343def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6344    """
6345    Wrap a condition with a NOT operator.
6346
6347    Example:
6348        >>> not_("this_suit='black'").sql()
6349        "NOT this_suit = 'black'"
6350
6351    Args:
6352        expression: the SQL code string to parse.
6353            If an Expression instance is passed, this is used as-is.
6354        dialect: the dialect used to parse the input expression.
6355        copy: whether to copy the expression or not.
6356        **opts: other options to use to parse the input expressions.
6357
6358    Returns:
6359        The new condition.
6360    """
6361    this = condition(
6362        expression,
6363        dialect=dialect,
6364        copy=copy,
6365        **opts,
6366    )
6367    return Not(this=_wrap(this, Connector))
6368
6369
6370def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6371    """
6372    Wrap an expression in parentheses.
6373
6374    Example:
6375        >>> paren("5 + 3").sql()
6376        '(5 + 3)'
6377
6378    Args:
6379        expression: the SQL code string to parse.
6380            If an Expression instance is passed, this is used as-is.
6381        copy: whether to copy the expression or not.
6382
6383    Returns:
6384        The wrapped expression.
6385    """
6386    return Paren(this=maybe_parse(expression, copy=copy))
6387
6388
6389SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6390
6391
6392@t.overload
6393def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6394
6395
6396@t.overload
6397def to_identifier(
6398    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6399) -> Identifier: ...
6400
6401
6402def to_identifier(name, quoted=None, copy=True):
6403    """Builds an identifier.
6404
6405    Args:
6406        name: The name to turn into an identifier.
6407        quoted: Whether to force quote the identifier.
6408        copy: Whether to copy name if it's an Identifier.
6409
6410    Returns:
6411        The identifier ast node.
6412    """
6413
6414    if name is None:
6415        return None
6416
6417    if isinstance(name, Identifier):
6418        identifier = maybe_copy(name, copy)
6419    elif isinstance(name, str):
6420        identifier = Identifier(
6421            this=name,
6422            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6423        )
6424    else:
6425        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6426    return identifier
6427
6428
6429def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6430    """
6431    Parses a given string into an identifier.
6432
6433    Args:
6434        name: The name to parse into an identifier.
6435        dialect: The dialect to parse against.
6436
6437    Returns:
6438        The identifier ast node.
6439    """
6440    try:
6441        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6442    except ParseError:
6443        expression = to_identifier(name)
6444
6445    return expression
6446
6447
6448INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6449
6450
6451def to_interval(interval: str | Literal) -> Interval:
6452    """Builds an interval expression from a string like '1 day' or '5 months'."""
6453    if isinstance(interval, Literal):
6454        if not interval.is_string:
6455            raise ValueError("Invalid interval string.")
6456
6457        interval = interval.this
6458
6459    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6460
6461    if not interval_parts:
6462        raise ValueError("Invalid interval string.")
6463
6464    return Interval(
6465        this=Literal.string(interval_parts.group(1)),
6466        unit=Var(this=interval_parts.group(2).upper()),
6467    )
6468
6469
6470@t.overload
6471def to_table(sql_path: str | Table, **kwargs) -> Table: ...
6472
6473
6474@t.overload
6475def to_table(sql_path: None, **kwargs) -> None: ...
6476
6477
6478def to_table(
6479    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6480) -> t.Optional[Table]:
6481    """
6482    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6483    If a table is passed in then that table is returned.
6484
6485    Args:
6486        sql_path: a `[catalog].[schema].[table]` string.
6487        dialect: the source dialect according to which the table name will be parsed.
6488        copy: Whether to copy a table if it is passed in.
6489        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6490
6491    Returns:
6492        A table expression.
6493    """
6494    if sql_path is None or isinstance(sql_path, Table):
6495        return maybe_copy(sql_path, copy=copy)
6496    if not isinstance(sql_path, str):
6497        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6498
6499    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6500    if table:
6501        for k, v in kwargs.items():
6502            table.set(k, v)
6503
6504    return table
6505
6506
6507def to_column(sql_path: str | Column, **kwargs) -> Column:
6508    """
6509    Create a column from a `[table].[column]` sql path. Schema is optional.
6510
6511    If a column is passed in then that column is returned.
6512
6513    Args:
6514        sql_path: `[table].[column]` string
6515    Returns:
6516        Table: A column expression
6517    """
6518    if sql_path is None or isinstance(sql_path, Column):
6519        return sql_path
6520    if not isinstance(sql_path, str):
6521        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6522    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6523
6524
6525def alias_(
6526    expression: ExpOrStr,
6527    alias: t.Optional[str | Identifier],
6528    table: bool | t.Sequence[str | Identifier] = False,
6529    quoted: t.Optional[bool] = None,
6530    dialect: DialectType = None,
6531    copy: bool = True,
6532    **opts,
6533):
6534    """Create an Alias expression.
6535
6536    Example:
6537        >>> alias_('foo', 'bar').sql()
6538        'foo AS bar'
6539
6540        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6541        '(SELECT 1, 2) AS bar(a, b)'
6542
6543    Args:
6544        expression: the SQL code strings to parse.
6545            If an Expression instance is passed, this is used as-is.
6546        alias: the alias name to use. If the name has
6547            special characters it is quoted.
6548        table: Whether to create a table alias, can also be a list of columns.
6549        quoted: whether to quote the alias
6550        dialect: the dialect used to parse the input expression.
6551        copy: Whether to copy the expression.
6552        **opts: other options to use to parse the input expressions.
6553
6554    Returns:
6555        Alias: the aliased expression
6556    """
6557    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6558    alias = to_identifier(alias, quoted=quoted)
6559
6560    if table:
6561        table_alias = TableAlias(this=alias)
6562        exp.set("alias", table_alias)
6563
6564        if not isinstance(table, bool):
6565            for column in table:
6566                table_alias.append("columns", to_identifier(column, quoted=quoted))
6567
6568        return exp
6569
6570    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6571    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6572    # for the complete Window expression.
6573    #
6574    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6575
6576    if "alias" in exp.arg_types and not isinstance(exp, Window):
6577        exp.set("alias", alias)
6578        return exp
6579    return Alias(this=exp, alias=alias)
6580
6581
6582def subquery(
6583    expression: ExpOrStr,
6584    alias: t.Optional[Identifier | str] = None,
6585    dialect: DialectType = None,
6586    **opts,
6587) -> Select:
6588    """
6589    Build a subquery expression.
6590
6591    Example:
6592        >>> subquery('select x from tbl', 'bar').select('x').sql()
6593        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6594
6595    Args:
6596        expression: the SQL code strings to parse.
6597            If an Expression instance is passed, this is used as-is.
6598        alias: the alias name to use.
6599        dialect: the dialect used to parse the input expression.
6600        **opts: other options to use to parse the input expressions.
6601
6602    Returns:
6603        A new Select instance with the subquery expression included.
6604    """
6605
6606    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6607    return Select().from_(expression, dialect=dialect, **opts)
6608
6609
6610@t.overload
6611def column(
6612    col: str | Identifier,
6613    table: t.Optional[str | Identifier] = None,
6614    db: t.Optional[str | Identifier] = None,
6615    catalog: t.Optional[str | Identifier] = None,
6616    *,
6617    fields: t.Collection[t.Union[str, Identifier]],
6618    quoted: t.Optional[bool] = None,
6619    copy: bool = True,
6620) -> Dot:
6621    pass
6622
6623
6624@t.overload
6625def column(
6626    col: str | Identifier,
6627    table: t.Optional[str | Identifier] = None,
6628    db: t.Optional[str | Identifier] = None,
6629    catalog: t.Optional[str | Identifier] = None,
6630    *,
6631    fields: Lit[None] = None,
6632    quoted: t.Optional[bool] = None,
6633    copy: bool = True,
6634) -> Column:
6635    pass
6636
6637
6638def column(
6639    col,
6640    table=None,
6641    db=None,
6642    catalog=None,
6643    *,
6644    fields=None,
6645    quoted=None,
6646    copy=True,
6647):
6648    """
6649    Build a Column.
6650
6651    Args:
6652        col: Column name.
6653        table: Table name.
6654        db: Database name.
6655        catalog: Catalog name.
6656        fields: Additional fields using dots.
6657        quoted: Whether to force quotes on the column's identifiers.
6658        copy: Whether to copy identifiers if passed in.
6659
6660    Returns:
6661        The new Column instance.
6662    """
6663    this = Column(
6664        this=to_identifier(col, quoted=quoted, copy=copy),
6665        table=to_identifier(table, quoted=quoted, copy=copy),
6666        db=to_identifier(db, quoted=quoted, copy=copy),
6667        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6668    )
6669
6670    if fields:
6671        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6672    return this
6673
6674
6675def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6676    """Cast an expression to a data type.
6677
6678    Example:
6679        >>> cast('x + 1', 'int').sql()
6680        'CAST(x + 1 AS INT)'
6681
6682    Args:
6683        expression: The expression to cast.
6684        to: The datatype to cast to.
6685        copy: Whether to copy the supplied expressions.
6686
6687    Returns:
6688        The new Cast instance.
6689    """
6690    expression = maybe_parse(expression, copy=copy, **opts)
6691    data_type = DataType.build(to, copy=copy, **opts)
6692    expression = Cast(this=expression, to=data_type)
6693    expression.type = data_type
6694    return expression
6695
6696
6697def table_(
6698    table: Identifier | str,
6699    db: t.Optional[Identifier | str] = None,
6700    catalog: t.Optional[Identifier | str] = None,
6701    quoted: t.Optional[bool] = None,
6702    alias: t.Optional[Identifier | str] = None,
6703) -> Table:
6704    """Build a Table.
6705
6706    Args:
6707        table: Table name.
6708        db: Database name.
6709        catalog: Catalog name.
6710        quote: Whether to force quotes on the table's identifiers.
6711        alias: Table's alias.
6712
6713    Returns:
6714        The new Table instance.
6715    """
6716    return Table(
6717        this=to_identifier(table, quoted=quoted) if table else None,
6718        db=to_identifier(db, quoted=quoted) if db else None,
6719        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6720        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6721    )
6722
6723
6724def values(
6725    values: t.Iterable[t.Tuple[t.Any, ...]],
6726    alias: t.Optional[str] = None,
6727    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6728) -> Values:
6729    """Build VALUES statement.
6730
6731    Example:
6732        >>> values([(1, '2')]).sql()
6733        "VALUES (1, '2')"
6734
6735    Args:
6736        values: values statements that will be converted to SQL
6737        alias: optional alias
6738        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6739         If either are provided then an alias is also required.
6740
6741    Returns:
6742        Values: the Values expression object
6743    """
6744    if columns and not alias:
6745        raise ValueError("Alias is required when providing columns")
6746
6747    return Values(
6748        expressions=[convert(tup) for tup in values],
6749        alias=(
6750            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6751            if columns
6752            else (TableAlias(this=to_identifier(alias)) if alias else None)
6753        ),
6754    )
6755
6756
6757def var(name: t.Optional[ExpOrStr]) -> Var:
6758    """Build a SQL variable.
6759
6760    Example:
6761        >>> repr(var('x'))
6762        'Var(this=x)'
6763
6764        >>> repr(var(column('x', table='y')))
6765        'Var(this=x)'
6766
6767    Args:
6768        name: The name of the var or an expression who's name will become the var.
6769
6770    Returns:
6771        The new variable node.
6772    """
6773    if not name:
6774        raise ValueError("Cannot convert empty name into var.")
6775
6776    if isinstance(name, Expression):
6777        name = name.name
6778    return Var(this=name)
6779
6780
6781def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6782    """Build ALTER TABLE... RENAME... expression
6783
6784    Args:
6785        old_name: The old name of the table
6786        new_name: The new name of the table
6787
6788    Returns:
6789        Alter table expression
6790    """
6791    old_table = to_table(old_name)
6792    new_table = to_table(new_name)
6793    return AlterTable(
6794        this=old_table,
6795        actions=[
6796            RenameTable(this=new_table),
6797        ],
6798    )
6799
6800
6801def rename_column(
6802    table_name: str | Table,
6803    old_column_name: str | Column,
6804    new_column_name: str | Column,
6805    exists: t.Optional[bool] = None,
6806) -> AlterTable:
6807    """Build ALTER TABLE... RENAME COLUMN... expression
6808
6809    Args:
6810        table_name: Name of the table
6811        old_column: The old name of the column
6812        new_column: The new name of the column
6813        exists: Whether to add the `IF EXISTS` clause
6814
6815    Returns:
6816        Alter table expression
6817    """
6818    table = to_table(table_name)
6819    old_column = to_column(old_column_name)
6820    new_column = to_column(new_column_name)
6821    return AlterTable(
6822        this=table,
6823        actions=[
6824            RenameColumn(this=old_column, to=new_column, exists=exists),
6825        ],
6826    )
6827
6828
6829def convert(value: t.Any, copy: bool = False) -> Expression:
6830    """Convert a python value into an expression object.
6831
6832    Raises an error if a conversion is not possible.
6833
6834    Args:
6835        value: A python object.
6836        copy: Whether to copy `value` (only applies to Expressions and collections).
6837
6838    Returns:
6839        Expression: the equivalent expression object.
6840    """
6841    if isinstance(value, Expression):
6842        return maybe_copy(value, copy)
6843    if isinstance(value, str):
6844        return Literal.string(value)
6845    if isinstance(value, bool):
6846        return Boolean(this=value)
6847    if value is None or (isinstance(value, float) and math.isnan(value)):
6848        return null()
6849    if isinstance(value, numbers.Number):
6850        return Literal.number(value)
6851    if isinstance(value, datetime.datetime):
6852        datetime_literal = Literal.string(
6853            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6854        )
6855        return TimeStrToTime(this=datetime_literal)
6856    if isinstance(value, datetime.date):
6857        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6858        return DateStrToDate(this=date_literal)
6859    if isinstance(value, tuple):
6860        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6861    if isinstance(value, list):
6862        return Array(expressions=[convert(v, copy=copy) for v in value])
6863    if isinstance(value, dict):
6864        return Map(
6865            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6866            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6867        )
6868    raise ValueError(f"Cannot convert {value}")
6869
6870
6871def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6872    """
6873    Replace children of an expression with the result of a lambda fun(child) -> exp.
6874    """
6875    for k, v in expression.args.items():
6876        is_list_arg = type(v) is list
6877
6878        child_nodes = v if is_list_arg else [v]
6879        new_child_nodes = []
6880
6881        for cn in child_nodes:
6882            if isinstance(cn, Expression):
6883                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6884                    new_child_nodes.append(child_node)
6885                    child_node.parent = expression
6886                    child_node.arg_key = k
6887            else:
6888                new_child_nodes.append(cn)
6889
6890        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6891
6892
6893def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6894    """
6895    Return all table names referenced through columns in an expression.
6896
6897    Example:
6898        >>> import sqlglot
6899        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6900        ['a', 'c']
6901
6902    Args:
6903        expression: expression to find table names.
6904        exclude: a table name to exclude
6905
6906    Returns:
6907        A list of unique names.
6908    """
6909    return {
6910        table
6911        for table in (column.table for column in expression.find_all(Column))
6912        if table and table != exclude
6913    }
6914
6915
6916def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6917    """Get the full name of a table as a string.
6918
6919    Args:
6920        table: Table expression node or string.
6921        dialect: The dialect to generate the table name for.
6922        identify: Determines when an identifier should be quoted. Possible values are:
6923            False (default): Never quote, except in cases where it's mandatory by the dialect.
6924            True: Always quote.
6925
6926    Examples:
6927        >>> from sqlglot import exp, parse_one
6928        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6929        'a.b.c'
6930
6931    Returns:
6932        The table name.
6933    """
6934
6935    table = maybe_parse(table, into=Table, dialect=dialect)
6936
6937    if not table:
6938        raise ValueError(f"Cannot parse {table}")
6939
6940    return ".".join(
6941        (
6942            part.sql(dialect=dialect, identify=True, copy=False)
6943            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6944            else part.name
6945        )
6946        for part in table.parts
6947    )
6948
6949
6950def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6951    """Returns a case normalized table name without quotes.
6952
6953    Args:
6954        table: the table to normalize
6955        dialect: the dialect to use for normalization rules
6956        copy: whether to copy the expression.
6957
6958    Examples:
6959        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6960        'A-B.c'
6961    """
6962    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6963
6964    return ".".join(
6965        p.name
6966        for p in normalize_identifiers(
6967            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6968        ).parts
6969    )
6970
6971
6972def replace_tables(
6973    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6974) -> E:
6975    """Replace all tables in expression according to the mapping.
6976
6977    Args:
6978        expression: expression node to be transformed and replaced.
6979        mapping: mapping of table names.
6980        dialect: the dialect of the mapping table
6981        copy: whether to copy the expression.
6982
6983    Examples:
6984        >>> from sqlglot import exp, parse_one
6985        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6986        'SELECT * FROM c /* a.b */'
6987
6988    Returns:
6989        The mapped expression.
6990    """
6991
6992    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6993
6994    def _replace_tables(node: Expression) -> Expression:
6995        if isinstance(node, Table):
6996            original = normalize_table_name(node, dialect=dialect)
6997            new_name = mapping.get(original)
6998
6999            if new_name:
7000                table = to_table(
7001                    new_name,
7002                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7003                    dialect=dialect,
7004                )
7005                table.add_comments([original])
7006                return table
7007        return node
7008
7009    return expression.transform(_replace_tables, copy=copy)
7010
7011
7012def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7013    """Replace placeholders in an expression.
7014
7015    Args:
7016        expression: expression node to be transformed and replaced.
7017        args: positional names that will substitute unnamed placeholders in the given order.
7018        kwargs: keyword arguments that will substitute named placeholders.
7019
7020    Examples:
7021        >>> from sqlglot import exp, parse_one
7022        >>> replace_placeholders(
7023        ...     parse_one("select * from :tbl where ? = ?"),
7024        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7025        ... ).sql()
7026        "SELECT * FROM foo WHERE str_col = 'b'"
7027
7028    Returns:
7029        The mapped expression.
7030    """
7031
7032    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7033        if isinstance(node, Placeholder):
7034            if node.name:
7035                new_name = kwargs.get(node.name)
7036                if new_name is not None:
7037                    return convert(new_name)
7038            else:
7039                try:
7040                    return convert(next(args))
7041                except StopIteration:
7042                    pass
7043        return node
7044
7045    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7046
7047
7048def expand(
7049    expression: Expression,
7050    sources: t.Dict[str, Query],
7051    dialect: DialectType = None,
7052    copy: bool = True,
7053) -> Expression:
7054    """Transforms an expression by expanding all referenced sources into subqueries.
7055
7056    Examples:
7057        >>> from sqlglot import parse_one
7058        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7059        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7060
7061        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7062        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7063
7064    Args:
7065        expression: The expression to expand.
7066        sources: A dictionary of name to Queries.
7067        dialect: The dialect of the sources dict.
7068        copy: Whether to copy the expression during transformation. Defaults to True.
7069
7070    Returns:
7071        The transformed expression.
7072    """
7073    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7074
7075    def _expand(node: Expression):
7076        if isinstance(node, Table):
7077            name = normalize_table_name(node, dialect=dialect)
7078            source = sources.get(name)
7079            if source:
7080                subquery = source.subquery(node.alias or name)
7081                subquery.comments = [f"source: {name}"]
7082                return subquery.transform(_expand, copy=False)
7083        return node
7084
7085    return expression.transform(_expand, copy=copy)
7086
7087
7088def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7089    """
7090    Returns a Func expression.
7091
7092    Examples:
7093        >>> func("abs", 5).sql()
7094        'ABS(5)'
7095
7096        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7097        'CAST(5 AS DOUBLE)'
7098
7099    Args:
7100        name: the name of the function to build.
7101        args: the args used to instantiate the function of interest.
7102        copy: whether to copy the argument expressions.
7103        dialect: the source dialect.
7104        kwargs: the kwargs used to instantiate the function of interest.
7105
7106    Note:
7107        The arguments `args` and `kwargs` are mutually exclusive.
7108
7109    Returns:
7110        An instance of the function of interest, or an anonymous function, if `name` doesn't
7111        correspond to an existing `sqlglot.expressions.Func` class.
7112    """
7113    if args and kwargs:
7114        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7115
7116    from sqlglot.dialects.dialect import Dialect
7117
7118    dialect = Dialect.get_or_raise(dialect)
7119
7120    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7121    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7122
7123    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7124    if constructor:
7125        if converted:
7126            if "dialect" in constructor.__code__.co_varnames:
7127                function = constructor(converted, dialect=dialect)
7128            else:
7129                function = constructor(converted)
7130        elif constructor.__name__ == "from_arg_list":
7131            function = constructor.__self__(**kwargs)  # type: ignore
7132        else:
7133            constructor = FUNCTION_BY_NAME.get(name.upper())
7134            if constructor:
7135                function = constructor(**kwargs)
7136            else:
7137                raise ValueError(
7138                    f"Unable to convert '{name}' into a Func. Either manually construct "
7139                    "the Func expression of interest or parse the function call."
7140                )
7141    else:
7142        kwargs = kwargs or {"expressions": converted}
7143        function = Anonymous(this=name, **kwargs)
7144
7145    for error_message in function.error_messages(converted):
7146        raise ValueError(error_message)
7147
7148    return function
7149
7150
7151def case(
7152    expression: t.Optional[ExpOrStr] = None,
7153    **opts,
7154) -> Case:
7155    """
7156    Initialize a CASE statement.
7157
7158    Example:
7159        case().when("a = 1", "foo").else_("bar")
7160
7161    Args:
7162        expression: Optionally, the input expression (not all dialects support this)
7163        **opts: Extra keyword arguments for parsing `expression`
7164    """
7165    if expression is not None:
7166        this = maybe_parse(expression, **opts)
7167    else:
7168        this = None
7169    return Case(this=this, ifs=[])
7170
7171
7172def cast_unless(
7173    expression: ExpOrStr,
7174    to: DATA_TYPE,
7175    *types: DATA_TYPE,
7176    **opts: t.Any,
7177) -> Expression | Cast:
7178    """
7179    Cast an expression to a data type unless it is a specified type.
7180
7181    Args:
7182        expression: The expression to cast.
7183        to: The data type to cast to.
7184        **types: The types to exclude from casting.
7185        **opts: Extra keyword arguments for parsing `expression`
7186    """
7187    expr = maybe_parse(expression, **opts)
7188    if expr.is_type(*types):
7189        return expr
7190    return cast(expr, to, **opts)
7191
7192
7193def array(
7194    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7195) -> Array:
7196    """
7197    Returns an array.
7198
7199    Examples:
7200        >>> array(1, 'x').sql()
7201        'ARRAY(1, x)'
7202
7203    Args:
7204        expressions: the expressions to add to the array.
7205        copy: whether to copy the argument expressions.
7206        dialect: the source dialect.
7207        kwargs: the kwargs used to instantiate the function of interest.
7208
7209    Returns:
7210        An array expression.
7211    """
7212    return Array(
7213        expressions=[
7214            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7215            for expression in expressions
7216        ]
7217    )
7218
7219
7220def tuple_(
7221    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7222) -> Tuple:
7223    """
7224    Returns an tuple.
7225
7226    Examples:
7227        >>> tuple_(1, 'x').sql()
7228        '(1, x)'
7229
7230    Args:
7231        expressions: the expressions to add to the tuple.
7232        copy: whether to copy the argument expressions.
7233        dialect: the source dialect.
7234        kwargs: the kwargs used to instantiate the function of interest.
7235
7236    Returns:
7237        A tuple expression.
7238    """
7239    return Tuple(
7240        expressions=[
7241            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7242            for expression in expressions
7243        ]
7244    )
7245
7246
7247def true() -> Boolean:
7248    """
7249    Returns a true Boolean expression.
7250    """
7251    return Boolean(this=True)
7252
7253
7254def false() -> Boolean:
7255    """
7256    Returns a false Boolean expression.
7257    """
7258    return Boolean(this=False)
7259
7260
7261def null() -> Null:
7262    """
7263    Returns a Null expression.
7264    """
7265    return Null()